Can you write this from memory?
Insert a new user into the users table with id 1, name 'Alice', and email 'alice@example.com'.
In SQL, UPDATE without WHERE affects every row. DELETE without WHERE empties the table. These aren't hypothetical mistakes—they happen to experienced engineers who are rushing.
Safe write workflow
BEGIN;
-- 1. Preview: run the same WHERE as a SELECT first
SELECT * FROM users WHERE last_login < '2024-01-01';
-- 2. Write with RETURNING to see what changed
DELETE FROM users WHERE last_login < '2024-01-01' RETURNING id, email;
-- 3. If wrong: ROLLBACK; If correct: COMMIT;
COMMIT;
Practice these patterns until they're automatic: preview first, use transactions, verify with RETURNING.
Before any UPDATE or DELETE in production, follow this pattern:
BEGIN;
-- Step 1: Preview what will be affected
SELECT * FROM users WHERE last_login < '2024-01-01';
-- Check the count and sample rows
-- Step 2: Run the write with RETURNING
DELETE FROM users
WHERE last_login < '2024-01-01'
RETURNING id, email, last_login;
-- Verify the output matches your expectations
-- Step 3: Decide
ROLLBACK; -- if anything looks wrong
COMMIT; -- if everything is correct
This workflow catches mistakes before they become disasters. The RETURNING clause shows exactly what changed, and the transaction lets you undo if needed.
Single row
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());
Multiple rows (batch insert)
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Carol', 'carol@example.com');
Multi-row inserts are significantly faster than individual INSERT statements—use them for bulk operations.
INSERT ... SELECT (copy from query)
INSERT...SELECT uses a subquery to populate rows from existing data:
-- Archive old records
INSERT INTO users_archive (id, name, email, archived_at)
SELECT id, name, email, NOW()
FROM users
WHERE last_login < '2023-01-01';
DEFAULT VALUES
-- Insert a row using all column defaults
INSERT INTO audit_log DEFAULT VALUES;
-- Mix explicit values with defaults
INSERT INTO users (name, email, role)
VALUES ('Alice', 'alice@example.com', DEFAULT);
INSERT ... RETURNING
-- Get the auto-generated ID back
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id;
-- Get multiple columns
INSERT INTO orders (user_id, total)
VALUES (1, 99.99)
RETURNING id, created_at, total;
-- RETURNING * for all columns
INSERT INTO users (name) VALUES ('Bob') RETURNING *;
RETURNING eliminates the need for a separate SELECT to get generated values like IDs, timestamps, or computed columns.
Basic UPDATE with WHERE
UPDATE products
SET price = price * 1.1
WHERE category = 'electronics';
Always preview first:
SELECT id, name, price FROM products WHERE category = 'electronics';
UPDATE multiple columns
UPDATE users
SET
status = 'inactive',
deactivated_at = NOW(),
deactivated_reason = 'No login in 90 days'
WHERE last_login < CURRENT_DATE - INTERVAL '90 days';
UPDATE ... FROM (join update, PostgreSQL)
-- Update orders with data from another table
UPDATE orders o
SET
status = s.name,
status_updated_at = NOW()
FROM statuses s
WHERE o.status_id = s.id
AND o.status != s.name;
MySQL uses different syntax:
-- MySQL: UPDATE with JOIN
UPDATE orders o
JOIN statuses s ON o.status_id = s.id
SET o.status = s.name
WHERE o.status != s.name;
UPDATE ... RETURNING
UPDATE products
SET price = price * 0.9
WHERE stock > 100
RETURNING id, name, price AS new_price;
Basic DELETE with WHERE
DELETE FROM sessions
WHERE expires_at < NOW();
DELETE ... USING (join delete, PostgreSQL)
-- Delete orders for inactive customers
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id
AND c.inactive = true;
MySQL syntax:
-- MySQL: DELETE with JOIN
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.inactive = true;
DELETE ... RETURNING
-- See exactly what was deleted
DELETE FROM users
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '30 days'
RETURNING id, email, created_at;
DELETE with subquery
DELETE FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE inactive = true
);
Atomic archive pattern (DELETE + INSERT with CTE)
-- PostgreSQL: move rows atomically (archive and delete in one statement)
WITH archived AS (
DELETE FROM users
WHERE last_login < '2023-01-01'
RETURNING *
)
INSERT INTO users_archive
SELECT *, NOW() AS archived_at FROM archived;
This is more reliable than separate INSERT...SELECT + DELETE statements—if something fails, nothing is archived or deleted.
Soft delete (UPDATE instead of DELETE)
-- Instead of deleting, mark as inactive
UPDATE users
SET
deleted_at = NOW(),
status = 'deleted'
WHERE id = 123;
Soft deletes preserve data for auditing and allow recovery. Filter soft-deleted rows with WHERE deleted_at IS NULL in your queries.
"Upsert" = INSERT if new, UPDATE if exists. Handles unique constraint conflicts gracefully.
ON CONFLICT DO UPDATE (PostgreSQL)
INSERT INTO page_views (page_id, date, count)
VALUES (123, '2024-01-15', 1)
ON CONFLICT (page_id, date)
DO UPDATE SET count = page_views.count + EXCLUDED.count;
Key points:
ON CONFLICT (columns)must match a unique constraint or indexEXCLUDEDrefers to the row that would have been inserted- Use
table.columnto reference the existing row's values
ON CONFLICT DO NOTHING
-- Insert if not exists, silently skip if duplicate
INSERT INTO user_preferences (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id) DO NOTHING;
Caution: DO NOTHING silently drops duplicates. Use RETURNING to detect them:
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING
RETURNING id;
-- Empty result = conflict occurred, no insert
MySQL: ON DUPLICATE KEY UPDATE
-- Modern syntax (MySQL 8.0.19+): alias the VALUES row
INSERT INTO page_views (page_id, date, count)
VALUES (123, '2024-01-15', 1) AS new
ON DUPLICATE KEY UPDATE count = count + new.count;
-- Legacy syntax (deprecated in 8.0.20 but still works):
-- ON DUPLICATE KEY UPDATE count = count + VALUES(count);
SQLite: INSERT OR REPLACE / ON CONFLICT
-- Replace entire row on conflict
INSERT OR REPLACE INTO settings (key, value)
VALUES ('theme', 'dark');
-- Or use ON CONFLICT (SQLite 3.24+)
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = excluded.value;
| Operation | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
| UPSERT | ON CONFLICT DO UPDATE | ON DUPLICATE KEY UPDATE | ON CONFLICT or INSERT OR REPLACE |
| Join UPDATE | UPDATE ... FROM | UPDATE ... JOIN | UPDATE ... FROM (since 3.33) |
| Join DELETE | DELETE ... USING | DELETE ... JOIN | Use subquery |
| RETURNING | ✅ Full support | ❌ Limited | ✅ Since 3.35 |
| MERGE | ✅ Since v15 | ❌ | ❌ |
Our examples are PostgreSQL-first. The concepts apply across databases, but syntax varies. When in doubt, check your database's documentation.
Always use transactions for multi-statement writes
BEGIN;
-- Debit one account
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Credit another
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Both succeed or both fail
COMMIT;
Use SAVEPOINT for partial rollback
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 50);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
-- Oops, product 999 doesn't exist
ROLLBACK TO before_items;
-- Order still exists, just no items
COMMIT;
Check for uncommitted transactions
-- PostgreSQL: show open transactions
SELECT * FROM pg_stat_activity
WHERE state = 'idle in transaction';
Test yourself with these scenarios:
- Daily stats upsert: Insert a page view count for today, or increment if already exists.
- Bulk price update: Increase prices by 10% for products in the "premium" category.
- Archive old records: Copy users who haven't logged in for a year to an archive table, then delete them.
- Cascade update: Update order status and log the change in an audit table (single transaction).
- Conditional upsert: Insert a user preference, but only update if the new value is non-null.
- Join delete: Delete all orders for customers marked as "fraudulent".
- Safe delete: Delete expired sessions and return the count of affected rows.
When to Use SQL INSERT, UPDATE, DELETE, UPSERT
- Insert new records (single row, multi-row, or from a query).
- Update subsets of rows with precise WHERE clauses.
- Upsert to avoid duplicate rows on unique constraint conflicts.
- Delete with conditions, using RETURNING to confirm what was removed.
- Wrap multi-step writes in transactions for atomic rollback.
Check Your Understanding: SQL INSERT, UPDATE, DELETE, UPSERT
Insert a daily stats row or update it if it already exists.
Use INSERT ... ON CONFLICT (user_id, date) DO UPDATE SET count = stats.count + EXCLUDED.count. The EXCLUDED keyword references the values that would have been inserted.
What You'll Practice: SQL INSERT, UPDATE, DELETE, UPSERT
Common SQL INSERT, UPDATE, DELETE, UPSERT Pitfalls
- UPDATE/DELETE without WHERE affects all rows—preview with SELECT first
- Forgetting COMMIT leaves transactions open (locks held, changes invisible)
- ON CONFLICT requires a unique constraint or index on the conflict target
- UPDATE with JOIN can match the same row multiple times (watch cardinality)
- EXCLUDED only works inside ON CONFLICT DO UPDATE, not elsewhere
- Foreign key constraint violations block writes—check parent rows exist
- ON CONFLICT DO NOTHING silently drops duplicates—use RETURNING to detect them
SQL INSERT, UPDATE, DELETE, UPSERT FAQ
What is the biggest UPDATE/DELETE pitfall?
Forgetting the WHERE clause and affecting every row. Always preview with SELECT first, then copy the WHERE clause to your UPDATE/DELETE.
How do I preview what UPDATE/DELETE will touch?
Run the same query as a SELECT first: if your UPDATE has WHERE status = "inactive", run SELECT * FROM table WHERE status = "inactive" to see which rows match.
Should I use transactions for single statements?
For production writes, yes. Wrapping even single statements in BEGIN/COMMIT lets you ROLLBACK if RETURNING shows unexpected results. Multi-statement writes always need transactions.
What does RETURNING do?
RETURNING makes INSERT/UPDATE/DELETE return the affected rows (like a SELECT). Use RETURNING * to see all columns, or RETURNING id, name for specific ones. This confirms exactly what changed.
What is EXCLUDED in ON CONFLICT?
EXCLUDED refers to the row that would have been inserted before the conflict. In ON CONFLICT DO UPDATE, use EXCLUDED.column to reference the incoming values.
When should I use UPSERT vs MERGE?
Use ON CONFLICT DO UPDATE (Postgres) or ON DUPLICATE KEY UPDATE (MySQL) for simple upserts. MERGE (SQL standard, Postgres 15+) handles complex multi-condition logic but is more verbose. For most use cases, the database-specific upsert syntax is simpler.
How do I UPDATE with data from another table?
Use UPDATE ... FROM in PostgreSQL: UPDATE orders SET status = s.name FROM statuses s WHERE orders.status_id = s.id. MySQL uses UPDATE ... JOIN syntax instead.
How do I DELETE with a JOIN condition?
Use DELETE ... USING in PostgreSQL: DELETE FROM orders USING customers WHERE orders.customer_id = customers.id AND customers.inactive = true. MySQL uses DELETE ... JOIN.
Can UPDATE/DELETE with JOIN affect the same row multiple times?
Yes—if the JOIN produces multiple matches, the row may be updated multiple times (last write wins) or the behavior may be undefined. Always ensure your JOIN conditions produce unique matches.
How do I insert multiple rows efficiently?
Use multi-row VALUES: INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6). Or INSERT ... SELECT for bulk inserts from queries. Both are faster than multiple single-row INSERTs.
SQL INSERT, UPDATE, DELETE, UPSERT Syntax Quick Reference
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Carol', 'carol@example.com');INSERT INTO archive (user_id, email)
SELECT id, email FROM users
WHERE last_login < '2023-01-01';INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;UPDATE products
SET price = price * 1.1
WHERE category = 'electronics'
RETURNING id, name, price;-- PostgreSQL: update from another table
UPDATE orders
SET status = s.name
FROM statuses s
WHERE orders.status_id = s.id;DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id;-- PostgreSQL: delete with join condition
DELETE FROM orders
USING customers c
WHERE orders.customer_id = c.id
AND c.inactive = true;INSERT INTO stats (user_id, date, count)
VALUES (1, '2024-01-15', 1)
ON CONFLICT (user_id, date)
DO UPDATE SET count = stats.count + EXCLUDED.count;BEGIN;
-- Preview first
SELECT * FROM users WHERE status = 'inactive';
-- Then delete with verification
DELETE FROM users WHERE status = 'inactive' RETURNING id;
-- ROLLBACK; if wrong, COMMIT; if correct
COMMIT;SQL INSERT, UPDATE, DELETE, UPSERT Sample Exercises
Fill in the keyword to insert data into the products table
INSERTIf the users table has columns (id, name, email, phone) where phone allows NULL, what value will phone have after: INSERT INTO users (id, name, email) VALUES (1, 'Bob', 'bob@test.com')?
NULLFill in the separator between the two value sets
,+ 15 more exercises