INSERT Patterns

Single and Bulk Insert 

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

INSERT INTO users (name, email) VALUES
    ('Bob', '[email protected]'),
    ('Carol', '[email protected]'),
    ('Dave', '[email protected]');

Bulk inserts in a single statement are faster than individual inserts — one parse, one commit (with autocommit on).

INSERT … ON DUPLICATE KEY UPDATE 

  INSERT INTO inventory (sku, quantity, warehouse)
VALUES ('SKU-001', 100, 'east')
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity),
    updated_at = NOW();

Requires a UNIQUE index on the conflict columns (sku + warehouse composite unique key).

INSERT IGNORE 

  INSERT IGNORE INTO users (email, name) VALUES ('[email protected]', 'Duplicate');
-- Silently skips on duplicate key — use cautiously; errors become invisible

Prefer ON DUPLICATE KEY UPDATE for explicit conflict handling.

INSERT FROM SELECT 

  INSERT INTO orders_archive (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

Run SELECT COUNT(*) with the same WHERE first to estimate row count and lock duration.

SELECT with Advanced Filtering 

  -- Comparison and range
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM users WHERE id IN (1, 5, 12, 99);

-- Pattern matching (index-friendly only with prefix)
SELECT * FROM users WHERE email LIKE 'admin%';   -- uses index
SELECT * FROM users WHERE email LIKE '%admin%';  -- full scan

-- Conditional logic
SELECT name,
    CASE status
        WHEN 'active' THEN 'OK'
        WHEN 'inactive' THEN 'Dormant'
        ELSE 'Unknown'
    END AS status_label
FROM users;

Pagination Strategies 

  -- Offset pagination (simple, slow at high offsets)
SELECT id, name FROM users ORDER BY id LIMIT 20 OFFSET 100000;

-- Keyset pagination (fast at any depth)
SELECT id, name FROM users WHERE id > 100000 ORDER BY id LIMIT 20;

Keyset pagination requires a unique, indexed sort column and cannot jump to arbitrary pages easily.

UPDATE Safely 

  -- Step 1: Preview affected rows
SELECT id, status FROM users WHERE last_login_at < '2023-01-01';

-- Step 2: Update with identical WHERE
UPDATE users SET status = 'inactive' WHERE last_login_at < '2023-01-01';

-- Step 3: Verify
SELECT ROW_COUNT();  -- rows changed in current session

Conditional Update 

  UPDATE products
SET stock = stock - 1
WHERE id = 42 AND stock > 0;
-- Check ROW_COUNT() = 1 to confirm successful decrement

Join Update 

  UPDATE orders o
INNER JOIN users u ON o.user_id = u.id
SET o.discount_pct = 10
WHERE u.country = 'US' AND o.status = 'pending';

DELETE Patterns 

  -- Simple delete
DELETE FROM sessions WHERE expires_at < NOW();

-- Join delete
DELETE oi FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'cancelled' AND o.created_at < '2020-01-01';

-- Soft delete (preferred for audit)
UPDATE users SET deleted_at = NOW(), status = 'deleted' WHERE id = 5;
SELECT * FROM users WHERE deleted_at IS NULL;  -- active users only

TRUNCATE vs DELETE 

  TRUNCATE TABLE temp_staging;  -- fast, resets AUTO_INCREMENT, DDL not DML
DELETE FROM temp_staging;     -- slower, row-by-row, can rollback in transaction

TRUNCATE cannot be rolled back in some configurations and fires no row-level triggers.

REPLACE 

  REPLACE INTO settings (key_name, value) VALUES ('theme', 'dark');
-- DELETE existing row + INSERT new row on duplicate key

REPLACE changes AUTO_INCREMENT behavior and fires DELETE triggers — prefer upsert syntax.

Transactional CRUD 

  START TRANSACTION;

INSERT INTO orders (user_id, total, status) VALUES (1, 149.99, 'pending');
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (@order_id, 10, 2, 49.99),
    (@order_id, 25, 1, 50.01);

UPDATE products SET stock = stock - 2 WHERE id = 10 AND stock >= 2;
UPDATE products SET stock = stock - 1 WHERE id = 25 AND stock >= 1;

-- Verify stock updates succeeded
-- If ROW_COUNT() = 0 for any update, ROLLBACK

COMMIT;

Prepared Statements 

  PREPARE stmt FROM 'SELECT * FROM users WHERE email = ? AND status = ?';
SET @email = '[email protected]';
SET @status = 'active';
EXECUTE stmt USING @email, @status;
DEALLOCATE PREPARE stmt;

Application drivers use prepared statements automatically — this prevents SQL injection and enables query plan caching.

Best Practices

  1. Always scope UPDATE and DELETE with WHERE — use LIMIT for batch deletes on large tables
  2. Batch large deletes to avoid long locks: DELETE ... LIMIT 1000 in a loop
  3. Use transactions when multiple tables must stay consistent
  4. Return affected row counts to callers — 0 rows updated may indicate a bug
  5. Log destructive operations with actor and timestamp

Common Mistakes

Mistake Consequence
Missing WHERE on UPDATE/DELETE Catastrophic data loss
Upsert without unique index Silent duplicate inserts
Large DELETE in one transaction Binlog bloat, replication lag, lock waits
Reading after write on replica Stale data — route critical reads to primary

Troubleshooting

Deadlock on concurrent updates:

  SHOW ENGINE INNODB STATUS\G
-- Look for LATEST DETECTED DEADLOCK section
-- Fix: consistent lock ordering, shorter transactions, retry logic

“Duplicate entry” on INSERT:

  SHOW INDEX FROM users;
-- Identify conflicting unique key; use ON DUPLICATE KEY UPDATE or fix data

Production Scenario

An nightly job archives old sessions in batches:

  -- Repeat until ROW_COUNT() = 0
INSERT INTO sessions_archive SELECT * FROM sessions WHERE expires_at < NOW() LIMIT 5000;
DELETE FROM sessions WHERE expires_at < NOW() LIMIT 5000;
COMMIT;

Each batch commits separately to avoid locking the sessions table for minutes during peak hours in other time zones.

Master CRUD patterns with safety checks and transactions — they are the daily operations that keep production data correct under load.