HowToCodePage

INSERT, UPDATE, DELETE

Changing data safely: returning clauses, transactions, upserts, and the missing WHERE clause everybody learns once.

Inserting

INSERT INTO products (name, price, category)
VALUES ('Desk lamp', 39.90, 'home');

-- several rows at once
INSERT INTO products (name, price) VALUES
  ('Cable', 9.9), ('Adapter', 14.5);

-- copy from another table
INSERT INTO archive (id, name)
SELECT id, name FROM products WHERE discontinued = TRUE;

-- get generated keys back (PostgreSQL)
INSERT INTO products (name) VALUES ('Mat')
RETURNING id, name;
💡
Always list the target columns explicitly. Without them your statement breaks the moment a column is added, reordered, or dropped.

Updating and deleting

UPDATE products
SET price = price * 0.9, updated_at = NOW()
WHERE category = 'home'
RETURNING id, price;

DELETE FROM products
WHERE discontinued = TRUE AND stock = 0;
⚠️
A missing WHERE applies the statement to every row. Habits that help: write the WHERE first, wrap ad-hoc changes in a transaction you can roll back, and preview with SELECT using the same predicate.

Upserts

-- PostgreSQL: insert, or update on conflict
INSERT INTO settings (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme, updated_at = NOW();

-- MySQL equivalent
INSERT INTO settings (user_id, theme) VALUES (1, 'dark')
ON DUPLICATE KEY UPDATE theme = VALUES(theme);

Upserts need a unique constraint or index to detect the conflict — without one nothing conflicts, and you simply duplicate rows.

Transactions

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK to undo everything

A transaction groups statements so they either all succeed or none do — essential whenever two rows must stay consistent (money transfers, inventory).

FAQ

TRUNCATE or DELETE?
TRUNCATE removes all rows quickly and usually cannot be rolled back in MySQL; DELETE is logged row by row and respects WHERE and triggers.
How do I delete duplicates?
Identify them with GROUP BY … HAVING COUNT(*) > 1, then delete using a unique identifier — often easiest with a window function numbering rows per key.

SELECT: reading data Indexes and query speed

Last refreshed 2026-09-17.