Updating and deleting: UPDATE and DELETE

UPDATE modifies existing rows; DELETE removes them. Both share the same, fundamental, golden rule: the WHERE clause decides which rows to touch. Forgetting it means modifying/deleting the whole table.

UPDATE

UPDATE tabella
SET    colonna1 = nuovo_valore,
       colonna2 = altra_espressione
WHERE  condizione;

Examples:

-- Aumenta del 10% i prezzi di tutti i prodotti della categoria 5 (Computer):
UPDATE products
SET    price = price * 1.10
WHERE  category_id = 5;

-- Aggiorna due colonne contemporaneamente:
UPDATE orders
SET    status     = 'shipped',
       shipped_at = NOW()
WHERE  id = 12;

SET col = espressione: the expression can reference the row's current values (price * 1.10), and can also use NOW(), NULL, or sub-queries.

DELETE

DELETE FROM tabella
WHERE  condizione;

Examples:

-- Cancella tutte le recensioni con rating < 3:
DELETE FROM reviews
WHERE  rating < 3;

-- Cancella un ordine specifico:
DELETE FROM orders WHERE id = 999;

RETURNING here too

Like INSERT, UPDATE and DELETE also accept RETURNING so you know what they acted on:

DELETE FROM reviews
WHERE  rating < 3
RETURNING id, customer_id, rating;

Useful for logging in one shot what was deleted/modified.

Foreign keys and deletion

Trying to delete a row referenced by another table through a foreign key produces an error:

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- on table "orders"

Solutions: delete the child rows first, or define the FK with ON DELETE CASCADE when you create the table (we'll cover this in the next DDL lesson).

Your turn

Review exercise