INSERT / UPDATE / DELETE

Read is sightseeing. Write is when you start caring about constraints and "oops I deleted prod".

Same playground contacts table. In real apps these map to POST / PATCH / DELETE - still the same SQL underneath.

INSERT

INSERT INTO contacts (name, email, city)
VALUES ('Katherine Johnson', 'kathy@example.com', 'White Sulphur Springs');

Multiple rows:

INSERT INTO contacts (name, email, city) VALUES
  ('Margaret Hamilton', 'margaret@example.com', 'Cambridge'),
  ('Annie Easley', 'annie@example.com', 'Birmingham');

Skip a column and it becomes NULL (unless there's a DEFAULT or NOT NULL without default - then it errors).

INSERT INTO contacts (name, email)
VALUES ('No City Yet', 'nocity@example.com');
-- city is NULL

Get the new id (SQLite):

SELECT last_insert_rowid();

UPDATE

UPDATE contacts
SET city = 'London'
WHERE email = 'kathy@example.com';

Comments for the code above:

• Always use a WHERE unless you truly mean "update every row"
• You can set multiple columns: SET city = 'x', name = 'y'
• No WHERE + UPDATE = classic "I updated the whole table" story 😅

DELETE

DELETE FROM contacts
WHERE email = 'nocity@example.com';

Same rule: WHERE is your seatbelt.

-- nuclear option - every row gone, table remains
DELETE FROM contacts;

Soft deletes (keep the row, mark it deleted) are a UI/product pattern we'll use in the CRM chapters - deleted_at instead of actual DELETE.

Transactions (quick taste)

When several writes should succeed or fail together:

BEGIN;
UPDATE contacts SET city = 'Berlin' WHERE id = 1;
UPDATE companies SET name = 'New Name' WHERE id = 10;
COMMIT;
-- or ROLLBACK; if something went wrong

FE analogy: don't leave the UI half-updated if step 2 fails. Databases have the same idea built in.

Safety habits I actually use

1. SELECT the rows first with the same WHERE
2. Then UPDATE / DELETE with that WHERE
3. Prefer soft delete for user-facing "remove" in CRMs
4. Never concatenate user input into SQL strings - use bound parameters in app code (ORM / prepared statements)

Next: the weird cousin of every filter bug - Types & NULL.