INSERT, UPDATE and DELETE

Reading data is forgiving. A wrong SELECT gives you a wrong answer, you notice, and you fix the query. Writing data is not forgiving in the same way: a wrong UPDATE changes rows, and the previous values are gone.

The syntax here is short. The habits around it are the actual content of this lesson.

-- Step 1: see exactly which rows you are about to change
SELECT order_id, status, order_date
FROM orders
WHERE status = 'pending'
  AND order_date < '2026-01-01';

-- Step 2: the same WHERE, now as an UPDATE
UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
  AND order_date < '2026-01-01';

• The WHERE clause is character-for-character identical in both statements. That is the point — you verified this exact condition.
• The UPDATE reports how many rows it changed. Compare that number to the count you saw in step 1. If they differ, something changed underneath you and it is worth understanding why before continuing.

-- One row, columns named
INSERT INTO customers (company_name, email, country, credit_limit)
VALUES ('Harbour Electronics', 'accounts@harbour.example', 'Ireland', 5000.00);

-- Several rows in one statement
INSERT INTO products (product_name, category, unit_price, units_in_stock, discontinued)
VALUES
    ('USB-C Cable 2m',   'Cables',      9.99, 400, 0),
    ('HDMI Cable 3m',    'Cables',     12.50, 250, 0),
    ('Desk Microphone',  'Audio',      74.00,  30, 0);

-- Insert the result of a query
INSERT INTO archived_orders (order_id, customer_id, order_date, status)
SELECT o.order_id, o.customer_id, o.order_date, o.status
FROM orders AS o
WHERE o.order_date < '2024-01-01';

• customer_id is not in the first column list because it is an IDENTITY column — the database generates it. Supplying it would be rejected.
• Naming the columns means the statement keeps working when someone adds a column to the table. Omit the list and the values are matched by position, so a new column silently shifts everything.
• Multi-row INSERT is one statement and one round trip, which is meaningfully faster than three separate statements when you are loading many rows.
• INSERT ... SELECT copies rows between tables without pulling them into your application first.

There is no confirmation prompt and no warning. UPDATE products SET unit_price = 9.99 sets every product in the table to 9.99, in one statement, and reports success.

The WHERE clause is not optional in practice. It is the difference between changing one price and repricing your entire catalogue.

-- One row, identified by its primary key
UPDATE products
SET unit_price = 11.49
WHERE product_id = 318;

-- Several columns at once, with an expression
UPDATE products
SET unit_price = unit_price * 1.05,
    discontinued = 0
WHERE category = 'Cables';

-- Using a related table to decide which rows to change (SQL Server form)
UPDATE o
SET o.status = 'priority'
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.credit_limit >= 50000
  AND o.status = 'pending';

• SET unit_price = unit_price * 1.05 reads the current value and writes the result back, per row.
• The third statement is SQL Server's UPDATE ... FROM syntax. PostgreSQL writes UPDATE orders SET ... FROM customers WHERE ...; the standard form uses a correlated subquery. Check your database's syntax rather than assuming.
• A join in an UPDATE is where the SELECT-first habit earns its keep. If the join matches more rows than you expect, the UPDATE quietly changes all of them.

A transaction groups statements so they either all take effect or none do. While the transaction is open, you can inspect the result of your change and then decide: COMMIT to keep it, or ROLLBACK to undo it as though it never happened.

For a risky change this converts an irreversible mistake into a reversible one. Transactions get a full lesson later; this is the part you need now.

BEGIN TRANSACTION;

UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
  AND order_date < '2026-01-01';

-- Check the damage before making it permanent
SELECT COUNT(*) AS expired_now
FROM orders
WHERE status = 'expired';

-- If that number is right:
COMMIT;

-- If it is not:
-- ROLLBACK;

• Between BEGIN and COMMIT, your change is visible to your session and not to anyone else.
• ROLLBACK discards it entirely. The rows return to their previous values with no repair work.
• One caution: an open transaction holds locks on the rows it changed, so other users trying to read or write them wait. Do not leave a transaction open while you go for lunch.