UPDATE and DELETE — COMP 378

Change and remove

UPDATE changes columns in rows that match WHERE; DELETE removes matching rows. Both use RETURNING too:

UPDATE books SET price = price * 1.10
WHERE genre = 'poetry'
RETURNING title, price;

DELETE FROM reviews WHERE stars < 3 RETURNING id;

Note you can't DELETE a book that an order still references — the foreign key protects it. The database refuses to orphan the order_items rows that point at it. That's referential integrity doing its job.

UPDATE books SET price = price * 1.10 WHERE genre = 'poetry'. Two rows match, so two rows change — RETURNING title, price hands them straight back for checking.

A missing WHERE is not a syntax error and raises nothing — it simply matches every row. The only statement the database refuses outright is the one that would orphan existing rows.