Data Modification

INSERT, UPDATE, DELETE, and UPSERT — writing data safely

Why

Writes are irreversible unless you wrap them in a transaction. An UPDATE without a WHERE clause updates every row in the table — a mistake that has ended careers. Understanding DML (Data Manipulation Language) and how transactions protect you from these mistakes is essential before touching production data.

Intuition

DML statements (INSERT, UPDATE, DELETE) write data; DDL statements (CREATE, ALTER, DROP) change the schema. Both should be wrapped in a transaction when making changes that need to be atomic — either all succeed or all roll back. Always preview writes with a SELECT first using the same WHERE clause you plan to use.

Explanation

INSERT, UPDATE, DELETE

-- INSERT INSERT INTO users (name, email, created_at) VALUES ('Alice', 'alice@example.com', NOW()); -- INSERT from SELECT INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2023-01-01'; -- UPDATE — ALWAYS include WHERE unless you mean to update everything BEGIN; UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '1 year'; -- preview rows affected: rowcount shown COMMIT; -- or ROLLBACK; if something looks wrong -- DELETE — same discipline: WHERE required DELETE FROM sessions WHERE expires_at < NOW(); -- TRUNCATE — removes all rows instantly, no WHERE, no rollback in most DBs TRUNCATE TABLE temp_staging;

The pattern of wrapping UPDATE/DELETE in BEGIN ... COMMIT and keeping a ROLLBACK ready is standard practice for any manual data change in production. Run the equivalent SELECT first to confirm row count.

UPSERT — INSERT or UPDATE

-- Postgres: ON CONFLICT INSERT INTO user_stats (user_id, login_count, last_login) VALUES (42, 1, NOW()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_stats.login_count + 1, last_login = EXCLUDED.last_login; -- EXCLUDED refers to the row that was attempted to be inserted -- MySQL / SQLite: ON DUPLICATE KEY UPDATE / REPLACE INTO INSERT INTO user_stats (user_id, login_count) VALUES (42, 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1; -- SQL Server: MERGE MERGE INTO targets AS t USING sources AS s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.value = s.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

UPSERT is idempotent — safe to run multiple times, which makes it ideal for data pipelines where records may be re-ingested.

← Previous Next →