Writing data

insert

insert into doors values (…) without the column list works until somebody adds a column, and then it fails — or worse, quietly puts the values in the wrong places. The list is not optional in practice.

name the columns, every time

insert into doors (name, style, material, price, lead_days)
values ('Aurel', 'panel', 'oak', 1150, 11);

-- several rows in one statement: one round trip, one transaction
insert into doors (name, style, material, price, lead_days) values
  ('Aurel',  'panel', 'oak',   1150, 11),
  ('Marne',  'flush', 'steel',  700,  6);

-- insert the result of a query
insert into archive_orders (id, placed, status)
select id, placed, status from orders where placed < '2024-06-01';

Getting the new id back

insert … returning id in PostgreSQL and SQLite 3.35+. MySQL has last_insert_id(); SQLite also has last_insert_rowid(). Do not select max(id) — two users at once will give the same answer to both.

update, and the clause you must not forget

An update with no where changes every row, succeeds, and reports how many. There is no undo outside a transaction. Two habits prevent most of the damage: write the where first and the set second, and run it as a select before you run it as an update.

the where clause is the whole safety mechanism

update doors
set    price = round(price * 1.05, 2)
where  material = 'steel';               -- three rows

update doors set price = price * 1.05;   -- every door in the catalogue. no warning.

the same conditions, asked as a question first

select id, name, price, round(price * 1.05, 2) as new_price
from   doors where material = 'steel';    -- look at the three rows

update doors set price = round(price * 1.05, 2) where material = 'steel';

update from another table

copy a value across — the dialects differ

-- SQLite 3.33+ and PostgreSQL
update order_items as i
set    unit_price = d.price
from   doors d
where  d.id = i.door_id and i.order_id = 42;

-- everywhere, including MySQL and older SQLite
update order_items
set    unit_price = (select price from doors where doors.id = order_items.door_id)
where  order_id = 42;

delete

same shape, same danger

delete from order_items where order_id = 5;

delete from order_items;         -- empties the table
truncate table order_items;      -- the same, faster, and not in SQLite

Deleting is rarely what a business wants

A deleted order cannot be audited, refunded or explained. Most systems keep the row and set a flag or a deleted_at timestamp instead — a soft delete — and filter it out in the queries. Decide once, at the design stage, and stick to it.

Upsert: insert, or update if it is already there

excluded is the row you tried to insert. The alternative — select, then decide, then insert — is two statements with a gap in the middle, and two users can both find nothing and both insert.

one statement, no race

insert into doors (name, style, material, price, lead_days)
values ('Aurel', 'panel', 'oak', 1150, 11)
on conflict (name) do update set price = excluded.price;   -- SQLite, PostgreSQL

-- do nothing, if a duplicate is simply uninteresting
insert into doors (…) values (…) on conflict (name) do nothing;

-- MySQL
insert into doors (…) values (…) on duplicate key update price = values(price);

How many rows did that touch?

Every driver reports it: changes in SQLite, rowCount in node-postgres, affectedRows in MySQL. Checking it is how a migration notices that its where matched nothing, and how an application tells "updated" from "that row does not exist".