INSERT, UPDATE, DELETE

Change data safely, including transactions.

Queries read data; three statements change it. Because they change data, the golden rule is: write the WHERE clause first, and run it as a SELECT to check what it matches before you UPDATE or DELETE.

INSERT INTO customers (name, country, age)
VALUES ('Margaret', 'US', 52);

INSERT INTO customers (name, country, age) VALUES
  ('Alan', 'UK', 41),
  ('Barbara', 'US', 47);

Always name the columns so the statement survives table changes. Columns you omit receive their default (or NULL, or an auto-generated id).

UPDATE orders
SET status = 'shipped'
WHERE id = 10;

UPDATE customers
SET age = age + 1
WHERE country = 'UK';

DELETE FROM orders
WHERE status = 'refunded' AND total < 10;

DELETE removes rows; DROP TABLE removes the whole table. Many systems prefer a soft delete: add a deleted_at column and filter on it, so data can be recovered.

Transactions

A transaction groups statements so they succeed or fail together. Moving money must subtract from one account and add to another; if the second step fails, the first must be undone. Databases guarantee this through the ACID properties (atomicity, consistency, isolation, durability).

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- or ROLLBACK; to undo everything since BEGIN

Upsert

Sometimes you want "insert, or update if it already exists". PostgreSQL and SQLite support it:

INSERT INTO customers (id, name, country, age)
VALUES (1, 'Ada', 'UK', 37)
ON CONFLICT (id) DO UPDATE SET age = excluded.age;

BEGIN;
INSERT INTO products (name, price) VALUES ('Pen', 1.50), ('Gum', 0.50);
UPDATE products SET price = price * 1.10 WHERE name = 'Pen';
DELETE FROM products WHERE price < 1;
COMMIT;