SQL 2023: Changing data

Insert a row

Add one row, naming the columns.

INSERT INTO users (name, email) VALUES ('Ada', '[email protected]');

Insert from a query

Copy rows from a query into another table.

INSERT INTO archive (id, name) SELECT id, name FROM users WHERE active = false;

Update rows

Change columns in the rows that match.

UPDATE users SET active = false WHERE last_login < CURRENT_DATE - 365;

Caution: Without a WHERE clause, every row is updated. Run it as a SELECT first to see which rows match.

Delete rows

Remove the rows that match.

DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;

Caution: Without a WHERE clause, every row is deleted. Run it as a SELECT first to see which rows match.

Empty a table quickly

Remove all rows from a table at once.

TRUNCATE TABLE logs;

Caution: Deletes all rows immediately and can't be filtered. In some databases it can't be rolled back.

Insert or update (PostgreSQL and SQLite)

Insert a row, or update it if the key already exists.

INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Insert or update (MySQL)

MySQL's version of an upsert.

INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Group statements into a transaction

Make several changes succeed or fail together. Use ROLLBACK instead of COMMIT to undo them.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;