SQL INSERT, UPDATE, DELETE — DML That Stays Safe at Scale

The three write operations

INSERT adds new rows, UPDATE modifies existing ones, DELETE removes them. Together they are called DML (Data Manipulation Language). Unlike DDL, DML runs inside a transaction and can be rolled back.

INSERT — adding rows

-- Single row
INSERT INTO customers (email, display_name, country_code)
VALUES ('alice@example.com', 'Alice Brown', 'GB');

-- Multiple rows in one statement (faster than many single-row INSERTs)
INSERT INTO products (product_name, category, unit_price)
VALUES
    ('Mechanical Keyboard', 'Electronics', 89.99),
    ('Wireless Mouse',      'Electronics', 34.99),
    ('Standing Desk Mat',   'Office',      29.99);

-- Insert from a SELECT (copy rows between tables)
INSERT INTO archived_orders (id, customer_id, total_amount, created_at)
SELECT id, customer_id, total_amount, created_at
FROM   orders
WHERE  created_at < '2025-01-01'
  AND  status = 'completed';

UPSERT — insert or update on conflict

A common pattern: insert a row if it does not exist, update it if it does. Without a specific operator, this requires a SELECT first — which introduces a race condition. Use the database's native UPSERT instead.

-- Postgres: INSERT ... ON CONFLICT
INSERT INTO product_inventory (product_id, warehouse_id, stock_qty)
VALUES (42, 3, 100)
ON CONFLICT (product_id, warehouse_id) DO UPDATE
    SET stock_qty  = product_inventory.stock_qty + EXCLUDED.stock_qty,
        updated_at = NOW();
-- EXCLUDED refers to the row that was rejected (the proposed INSERT values)

-- Postgres: do nothing if a duplicate exists
INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (1001, 'theme', 'dark')
ON CONFLICT (user_id, setting_key) DO NOTHING;

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO product_inventory (product_id, warehouse_id, stock_qty)
VALUES (42, 3, 100)
ON DUPLICATE KEY UPDATE stock_qty = stock_qty + VALUES(stock_qty);

-- SQL Server: MERGE
MERGE INTO product_inventory AS tgt
USING (SELECT 42 AS product_id, 3 AS warehouse_id, 100 AS qty) AS src
    ON tgt.product_id = src.product_id AND tgt.warehouse_id = src.warehouse_id
WHEN MATCHED THEN
    UPDATE SET tgt.stock_qty = tgt.stock_qty + src.qty
WHEN NOT MATCHED THEN
    INSERT (product_id, warehouse_id, stock_qty) VALUES (src.product_id, src.warehouse_id, src.qty);

UPDATE — modifying existing rows

Always include a WHERE clause. An UPDATE without WHERE modifies every row in the table.

-- Update a single row
UPDATE orders SET status = 'shipped', updated_at = NOW()
WHERE id = 10842;

-- Update based on a related table (correlated update)
UPDATE order_items oi
SET unit_price = p.unit_price
FROM products p
WHERE p.id = oi.product_id
  AND oi.created_at >= '2026-01-01';

-- Conditional update (apply a discount to premium customers)
UPDATE orders
SET total_amount = total_amount * 0.90
WHERE customer_id IN (
    SELECT id FROM customers WHERE account_type = 'premium'
)
AND created_at >= '2026-06-01';

Batching large updates

Updating millions of rows in one statement holds a lock for the entire duration, blocking reads and writes. Batch it:

-- Batch update in chunks of 10,000 rows (Postgres)
DO $$
DECLARE
    updated INT := 1;
BEGIN
    WHILE updated > 0 LOOP
        UPDATE orders
        SET    status = 'archived'
        WHERE  id IN (
            SELECT id FROM orders
            WHERE  status = 'completed'
              AND  created_at < '2025-01-01'
            LIMIT  10000
        );
        GET DIAGNOSTICS updated = ROW_COUNT;
        PERFORM pg_sleep(0.1);  -- brief pause to allow other transactions
    END LOOP;
END $$;

DELETE — removing rows safely

Like UPDATE, always include WHERE. Test with SELECT first.

-- Verify before deleting
SELECT COUNT(*) FROM sessions WHERE expires_at < NOW();

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

-- Delete with a JOIN condition (Postgres syntax)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled'
  AND o.created_at < '2025-01-01';

DELETE vs TRUNCATE

-- Clear a staging table quickly (no row-by-row logging)
TRUNCATE TABLE staging_product_import;

-- TRUNCATE with cascade (also truncates referencing tables)
TRUNCATE TABLE orders CASCADE;  -- also clears order_items

RETURNING — get inserted/updated/deleted values

Postgres (and SQL Server with OUTPUT) can return the affected rows without a second query:

-- Get the new order's ID immediately after INSERT
INSERT INTO orders (customer_id, total_amount)
VALUES (1001, 249.99)
RETURNING id, created_at;

-- Update and return the new values
UPDATE products SET unit_price = unit_price * 1.05
WHERE category = 'Electronics'
RETURNING id, product_name, unit_price AS new_price;

Recap

Multi-row INSERT is significantly faster than many single-row statements. Use ON CONFLICT (Postgres) for UPSERT instead of a check-then-insert pattern. Always WHERE-qualify UPDATE and DELETE — test with SELECT first on new queries. Batch large updates to avoid long-held locks. Use TRUNCATE to clear staging tables, DELETE when you need transaction safety or triggers to fire.