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 ... WHEREMySQL 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 MATCHEDPostgres 15+ NEW Supports MERGE Postgres alt INSERT ... ON CONFLICT DO UPDATEMySQL INSERT ... ON DUPLICATE KEY UPDATESQLite 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