Manipulating Data with SQL DML
1. Inserting Single Rows
| Form | Example |
|---|---|
| Explicit columns | INSERT INTO t(a,b) VALUES (1,2); |
| All columns | INSERT INTO t VALUES (1,2,3); (fragile) |
| DEFAULT | INSERT INTO t(a) VALUES (DEFAULT); |
| RETURNING | INSERT ... RETURNING id; (PG, Oracle 23ai) |
2. Inserting Multiple Rows
Example: Multi-row Insert
INSERT INTO products(sku, name, price) VALUES
('A1','Widget', 9.99),
('A2','Gadget',19.99),
('A3','Gizmo', 29.99);| Tip | Detail |
|---|---|
| Batch size | 1k-10k per statement balances memory + network |
| COPY (Postgres) | 10–100× faster than INSERT for bulk |
| LOAD DATA (MySQL) | High-speed CSV loader |
3. Inserting from SELECT
| Form | Example |
|---|---|
| Copy data | INSERT INTO archive_orders SELECT * FROM orders WHERE placed_at < '2024-01-01'; |
| Transform | Use expressions in SELECT |
| CREATE TABLE AS | One-shot create + populate |
4. Updating Rows
| Form | Example |
|---|---|
| Single col | UPDATE t SET a=1 WHERE id=42; |
| Multiple cols | UPDATE t SET a=1, b=2 WHERE id=42; |
| RETURNING (PG) | Returns affected rows |
| Always WHERE | Missing WHERE updates ALL rows |
Warning: Always test UPDATE/DELETE as
SELECT ... WHERE ... first. In production, wrap in transactions with BEGIN/ROLLBACK rehearsal.5. Updating with Joins
Example: UPDATE FROM (Postgres)
UPDATE products p
SET price = p.price * 1.10
FROM categories c
WHERE p.category_id = c.id AND c.name = 'Electronics';| DB | Syntax |
|---|---|
| Postgres | UPDATE ... FROM ... WHERE |
| MySQL | UPDATE t1 JOIN t2 ON ... SET ... |
| SQL Server | UPDATE t1 SET ... FROM t1 JOIN t2 ... |
| Oracle | Use MERGE or updatable subquery |
6. Deleting Rows
| Form | Example |
|---|---|
| Basic | DELETE FROM t WHERE id=42; |
| With join (PG) | DELETE FROM t USING ref WHERE t.x=ref.y; |
| RETURNING (PG) | Get deleted rows |
| Soft delete | UPDATE t SET deleted_at = NOW() ... |
7. Truncating Tables
| Aspect | TRUNCATE vs DELETE |
|---|---|
| Speed | TRUNCATE: O(1) metadata op; DELETE: per-row |
| Triggers | TRUNCATE skips row triggers (Postgres can fire stmt triggers) |
| Transactional | Postgres: yes; MySQL InnoDB: yes; Oracle: implicit COMMIT |
| RESTART IDENTITY | Reset sequence values |
| CASCADE | Truncate dependent tables |
8. Using MERGE/UPSERT
| DB | Syntax |
|---|---|
| SQL standard | MERGE INTO ... USING ... ON ... WHEN MATCHED/NOT MATCHED |
| Postgres 15+ NEW | Supports MERGE |
| Postgres alt | INSERT ... ON CONFLICT DO UPDATE |
| MySQL | INSERT ... ON DUPLICATE KEY UPDATE |
| SQLite | INSERT ... ON CONFLICT ... or INSERT OR REPLACE |
Example: MERGE
MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name, updated_at = NOW()
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);9. Returning Affected Rows
| Mechanism | Detail |
|---|---|
| RETURNING (PG) | Get inserted/updated/deleted columns |
| OUTPUT (SQL Server) | Same concept |
| ROW_COUNT() | MySQL function: # rows affected |
| Driver API | cursor.rowcount, PreparedStatement.getUpdateCount |
10. Handling DML Errors
| Strategy | Detail |
|---|---|
| Transactions | BEGIN/COMMIT/ROLLBACK |
| SAVEPOINT | Partial rollback within tx |
| ON CONFLICT DO NOTHING | Skip violators silently |
| Try/catch (driver) | Map SQLSTATE → app errors |
| Retry on serialization failure | Detect 40001 and replay |