INSERT, UPDATE and DELETE

Modifying data, and the WHERE clause that saves your job.

What you'll be able to do

Insert single and multiple rows
Update and delete safely
Distinguish DELETE from TRUNCATE

INSERT

INSERT INTO customers (customer_id, email, name)
VALUES (1, '[email protected]', 'Ana');

INSERT INTO customers (customer_id, email, name) VALUES
    (2, '[email protected]', 'Ben'),
    (3, '[email protected]', 'Cai');

INSERT INTO customers_archive
SELECT * FROM customers WHERE created_at < '2020-01-01';

Always name the columns. INSERT INTO t VALUES (...) depends on column order and breaks silently when someone adds a column.

UPDATE

UPDATE customers
SET credit_limit = 5000,
    updated_at   = CURRENT_TIMESTAMP
WHERE customer_id = 1;

An UPDATE with no WHERE updates every row. There is no confirmation and no undo outside a transaction.

The habit that prevents disasters

Write it as a SELECT first:

-- 1. check what you are about to change
SELECT * FROM customers WHERE customer_id = 1;

-- 2. then convert to the UPDATE
UPDATE customers SET credit_limit = 5000 WHERE customer_id = 1;

Or wrap it in a transaction:

BEGIN;
UPDATE customers SET credit_limit = 5000 WHERE customer_id = 1;
-- check the row count, then:
COMMIT;   -- or ROLLBACK;

If the affected row count is not what you expected, roll back.

DELETE and TRUNCATE

DELETE FROM orders WHERE order_date < '2020-01-01';
TRUNCATE TABLE staging_orders;

DELETE removes selected rows, is logged, and can be rolled back. TRUNCATE empties the whole table, is much faster, and in most dialects cannot be rolled back or filtered.

Key points

UPDATE or DELETE without WHERE affects every row
Write the SELECT first, or wrap the change in a transaction
TRUNCATE is fast and unfiltered; DELETE is logged and reversible

Check yourself