SQL INSERT, UPDATE and DELETE: How to Change Data Safely

The short answer

Three statements change data in SQL:

• INSERT adds new rows.
• UPDATE changes values in existing rows.
• DELETE removes rows.

UPDATE and DELETE touch every row that matches the WHERE clause. If WHERE is wrong or missing, the statement still runs — on the wrong rows or on the whole table. Safety is mostly about checking that WHERE before the change becomes permanent.

INSERT: adding rows

INSERT INTO customers (name, email, city)
VALUES ('Aziza Karimova', 'aziza@example.com', 'Tashkent');

-- several rows at once
INSERT INTO customers (name, email, city)
VALUES ('Bekzod', 'bek@example.com', 'Samarkand'),
       ('Dilnoza', 'dil@example.com', 'Bukhara');

-- copy rows from a query
INSERT INTO archived_orders (id, total, created_at)
SELECT id, total, created_at FROM orders WHERE created_at < '2024-01-01';

Always list the columns explicitly. Without the column list, the insert depends on column order, and it breaks or silently writes values into the wrong columns when someone changes the table.

UPDATE: changing rows

UPDATE products
SET price = price * 1.1, updated_at = now()
WHERE category_id = 7;

SET can use the current value of a column, other columns and expressions. One statement can update thousands of rows, so the WHERE clause is the most important part.

DELETE: removing rows

DELETE FROM sessions
WHERE expires_at < now();

To empty a whole table, TRUNCATE is usually faster, but it skips row-level logic and is harder to undo. In many business tables, a soft delete (a deleted_at column) is safer than physically removing rows: data stays recoverable and history stays intact.

Safety habits that prevent disasters

1. Run SELECT with the same WHERE first

Before an UPDATE or DELETE, run a SELECT with exactly the same condition:

SELECT count(*) FROM orders WHERE status = 'draft' AND created_at < '2025-01-01';

If you expect 40 rows and see 40 000, stop. Then copy the WHERE clause into the change unchanged.

2. Wrap the change in a transaction

BEGIN;
UPDATE orders SET status = 'cancelled'
WHERE status = 'draft' AND created_at < '2025-01-01';
-- the client reports how many rows were affected
SELECT status, count(*) FROM orders GROUP BY status;
COMMIT;   -- or ROLLBACK if something looks wrong

Inside a transaction you can check the result and roll it back. Note that some tools (and MySQL with autocommit on) commit every statement immediately unless you explicitly start a transaction. Keep such transactions short: open transactions hold locks.

3. Use RETURNING to see what changed

PostgreSQL (and some other databases) can return the affected rows:

DELETE FROM coupons
WHERE expires_at < now()
RETURNING id, code;

It is a cheap audit trail and avoids a separate SELECT after INSERT to get generated IDs.

4. UPSERT instead of "check, then insert"

When a row may or may not exist, checking first and then inserting creates race conditions. Use one atomic statement instead. It requires a unique constraint on the key.

-- PostgreSQL
INSERT INTO stock (sku, qty) VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

-- MySQL
INSERT INTO stock (sku, qty) VALUES ('A-100', 5)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

Newer MySQL versions prefer a row alias over VALUES() in this clause; check your version's documentation.

The classic disaster: UPDATE without WHERE

UPDATE users SET password_hash = '...';   -- every user, not one

It usually happens when someone runs a highlighted fragment, forgets the last line or edits a query in a hurry. Protection:

• Write the WHERE clause first, then the SET part.
• In MySQL, enable safe updates mode (--safe-updates), which rejects UPDATE and DELETE without a key-based WHERE or LIMIT.
• Do not give application or analyst accounts more rights than needed; production changes go through reviewed scripts or migrations.
• Have tested backups and point-in-time recovery — a transaction does not help once the change is committed.

Common mistakes

• Running changes on production directly from a GUI with autocommit on.
• Updating in one huge statement that locks a large table for minutes; split it into batches.
• Building SQL by string concatenation instead of parameters — this opens the door to SQL injection.
• Deleting parent rows without understanding foreign keys and ON DELETE CASCADE.