SQL Data Manipulation Language (DML)

DML commands modify the data stored in tables without changing the table’s structure. The three core DML statements are INSERT (add rows), UPDATE (modify rows), and DELETE (remove rows).

DML statements are transactional — they can be wrapped in BEGIN / COMMIT / ROLLBACK for safety.

INSERT: Adding Rows

Single row insert:

INSERT INTO customers (name, email, city, created_at)
VALUES ('Alice Smith', 'alice@example.com', 'London', NOW());

Always specify the column list. If you omit it, the values must match every column in exactly the right order — fragile and breaks when columns are added.

Multiple rows in one statement:

INSERT INTO products (sku, name, price, category_id)
VALUES
    ('KB-001', 'Mechanical Keyboard', 149.99, 3),
    ('MO-022', 'Wireless Mouse', 79.99, 3),
    ('HS-007', 'USB-C Hub', 49.99, 5);

A single multi-row INSERT is significantly faster than multiple single-row inserts for bulk loading.

INSERT from a SELECT query:

-- Archive completed orders older than 2 years
INSERT INTO orders_archive (order_id, customer_id, amount, status, created_at)
SELECT order_id, customer_id, amount, status, created_at
FROM orders
WHERE status = 'completed'
  AND created_at < NOW() - INTERVAL '2 years';

INSERT with RETURNING (PostgreSQL):

-- Get the auto-generated ID back
INSERT INTO customers (name, email)
VALUES ('Bob Chen', 'bob@example.com')
RETURNING customer_id, name, created_at;

INSERT ON CONFLICT (UPSERT)

Insert a row, but if a conflict (duplicate key) occurs, update instead:

-- PostgreSQL: upsert on unique constraint
INSERT INTO product_inventory (product_id, quantity, last_updated)
VALUES (8821, 150, NOW())
ON CONFLICT (product_id) DO UPDATE
    SET quantity = EXCLUDED.quantity,
        last_updated = EXCLUDED.last_updated;

-- Do nothing on conflict (ignore duplicates)
INSERT INTO email_subscribers (email)
VALUES ('user@example.com')
ON CONFLICT (email) DO NOTHING;

-- MySQL equivalent
INSERT INTO product_inventory (product_id, quantity)
VALUES (8821, 150)
ON DUPLICATE KEY UPDATE quantity = VALUES(quantity);

EXCLUDED refers to the row that would have been inserted but was rejected by the conflict.

UPDATE: Modifying Existing Rows

Update specific rows:

UPDATE customers
SET email = 'alice.smith@newdomain.com',
    updated_at = NOW()
WHERE customer_id = 1042;

Update with computed values:

-- Apply a 15% discount to all electronics
UPDATE products
SET sale_price = price * 0.85,
    is_on_sale = TRUE,
    updated_at = NOW()
WHERE category = 'electronics';

-- Increment a counter
UPDATE page_views SET view_count = view_count + 1 WHERE page_id = 99;

Update from another table (PostgreSQL using FROM):

UPDATE orders o
SET customer_email = c.email
FROM customers c
WHERE o.customer_id = c.customer_id
  AND o.customer_email IS NULL;

Preview before updating:

-- Always run SELECT first to verify the WHERE clause targets the right rows
SELECT customer_id, email FROM customers WHERE domain = 'old-company.com';
-- If correct, then update:
UPDATE customers SET email = REPLACE(email, 'old-company.com', 'new-company.com')
WHERE email LIKE '%@old-company.com';

DELETE: Removing Rows

Delete with a condition:

DELETE FROM sessions WHERE expires_at < NOW();
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01';

Delete by primary key:

DELETE FROM customers WHERE customer_id = 1042;

Delete using a subquery (rows matching another table):

-- Delete orders for deactivated customers
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE is_active = FALSE
);

PostgreSQL: DELETE with USING (join-based delete):

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.customer_id
  AND c.account_status = 'suspended';

PostgreSQL: DELETE with RETURNING:

DELETE FROM audit_log
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING log_id, table_name, action, created_at;

TRUNCATE: Clear All Rows Fast

TRUNCATE removes all rows from a table without scanning them row by row — much faster than DELETE FROM table for large tables:

TRUNCATE TABLE staging_data;

-- Reset the auto-increment sequence too
TRUNCATE TABLE staging_data RESTART IDENTITY;

-- Cascade to clear dependent tables too
TRUNCATE TABLE customers CASCADE;  -- also truncates orders, sessions, etc.

Unlike DELETE, TRUNCATE doesn’t fire row-level triggers and isn’t easily rolled back in most databases (PostgreSQL is transactional for TRUNCATE).

Using Transactions for Safety

Wrap related DML statements in a transaction:

BEGIN;
    -- Transfer between accounts
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
    -- If either fails or something looks wrong:
    -- ROLLBACK;
COMMIT;

Transactions ensure atomicity — either both updates commit or neither does.

Test-and-rollback pattern:

BEGIN;
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01';
-- Verify what was deleted:
-- SELECT COUNT(*) FROM orders;
ROLLBACK;  -- undo to verify, then COMMIT when ready

DML Best Practices

Always use WHERE with UPDATE and DELETE. Without a WHERE clause, every row is affected.

Run SELECT first. Before any UPDATE or DELETE, run the equivalent SELECT to confirm the rows you’re targeting.

Use RETURNING for INSERT/UPDATE/DELETE. In PostgreSQL, RETURNING gives you back the affected rows — useful for auditing and chaining operations.

Batch large operations. Updating or deleting millions of rows at once locks the table. Process in chunks of 10,000–50,000 rows at a time.

Use transactions for multi-statement operations. Any sequence of statements that must succeed or fail together belongs inside BEGIN / COMMIT.