INSERT, UPDATE and DELETE

The three data-modification statements share one shape: pick a table, pick the rows with a WHERE, say what happens to them. The WHERE is the part that goes wrong.

INSERT

Always name the target columns. A column you leave out takes its DEFAULT (or NULL), and the statement keeps working when someone adds a column to the table.

INSERT INTO customers (id, name, country) VALUES (4, 'Mari', 'PL');

INSERT ... SELECT inserts the result of a query, which is how you copy, backfill or archive rows in one statement:

1CREATE TABLE customers (2  id      int PRIMARY KEY,3  name    text NOT NULL,4  country text NOT NULL5);6CREATE TABLE mailing_list (7  customer_id int  PRIMARY KEY,8  name        text NOT NULL,9  added_on    text NOT NULL DEFAULT 'import'10);11INSERT INTO customers VALUES (1, 'Nino', 'GE'), (2, 'Hans', 'DE'), (3, 'Ana', 'GE');12INSERT INTO mailing_list (customer_id, name) VALUES (1, 'Nino');

1INSERT INTO mailing_list (customer_id, name)2SELECT c.id, c.name3FROM customers c4WHERE c.country = 'GE'5  AND NOT EXISTS (SELECT 1 FROM mailing_list m6                  WHERE m.customer_id = c.id);7 8SELECT customer_id, name, added_on9FROM mailing_list10ORDER BY customer_id;

The NOT EXISTS makes the statement safe to run twice: Nino was already on the list and is not inserted again, and added_on took its default.

UPDATE from another table

UPDATE ... SET col = expr WHERE ... changes every row the WHERE matches; with no WHERE, every row in the table. To take the new value from another table, the standard form is a correlated subquery in SET:

1CREATE TABLE products (2  id    int PRIMARY KEY,3  name  text NOT NULL,4  price numeric(10,2)5);6CREATE TABLE price_changes (7  product_id int PRIMARY KEY,8  new_price  numeric(10,2) NOT NULL9);10INSERT INTO products VALUES (1, 'Notebook', 3.50), (2, 'Desk lamp', 24.00), (3, 'Pen', 1.20);11INSERT INTO price_changes VALUES (2, 19.99);

1UPDATE products2SET price = (SELECT c.new_price3             FROM price_changes c4             WHERE c.product_id = products.id);5 6SELECT id, name, price FROM products ORDER BY id;

That is the trap. The subquery returns no row for products without a price change, and a scalar subquery with no row is NULL — so the statement wiped two prices. The fix is a WHERE that limits the update to the rows that have a match:

UPDATE products
SET price = (SELECT c.new_price FROM price_changes c
             WHERE c.product_id = products.id)
WHERE EXISTS (SELECT 1 FROM price_changes c
              WHERE c.product_id = products.id);

Most engines also have a join form — UPDATE ... FROM in PostgreSQL and SQL Server, UPDATE a JOIN b in MySQL — which only touches matched rows. The syntax differs everywhere; the correlated form above works in all of them.

DELETE

DELETE FROM t WHERE ... removes the matching rows; related tables are usually tested with EXISTS:

1CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);2CREATE TABLE orders (3  id          int PRIMARY KEY,4  customer_id int  NOT NULL,5  status      text NOT NULL6);7INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');8INSERT INTO orders VALUES (10, 1, 'paid'), (11, 3, 'cancelled'), (12, 3, 'cancelled');

1DELETE FROM customers2WHERE NOT EXISTS (3  SELECT 1 FROM orders o4  WHERE o.customer_id = customers.id5    AND o.status <> 'cancelled'6);7 8SELECT id, name FROM customers ORDER BY id;

Ana has never ordered and Luka has only cancelled orders, so both are deleted; Nino stays. TRUNCATE empties a whole table faster, but it takes no WHERE, and it is not equally reversible everywhere: PostgreSQL and SQL Server can roll it back inside a transaction, while MySQL commits it on the spot. It is not a shortcut for a big DELETE.

Before you press enter

Run the WHERE as a SELECT COUNT(*) first. Then run the change inside a transaction — START TRANSACTION (or BEGIN), the statement, a look at the result, then COMMIT or ROLLBACK — so a surprising row count can still be undone. Transactions get a lesson of their own later in the course.

Common mistakes

• An UPDATE or DELETE with no WHERE. It is valid SQL and it touches every row.
• Updating from a correlated subquery without WHERE EXISTS. The rows with no match are set to NULL, as the example shows.
• INSERT INTO t VALUES (...) without a column list. It depends on the table's column order and breaks, or misplaces data, when a column is added.