UPDATE i DELETE

UPDATE

La instruccio UPDATE modifica els valors de columnes en files existents. Sempre ha d'anar acompanyada d'una clàusula WHERE (llevat que volgueu modificar totes les files, cosa que és molt poc freqüent i extremadament perillosa).

clients — ABANS

id_client	nom	email
41	Anna	anna·ex
42	Joan	vell·ex
43	Maria	maria·ex

→

clients — DESPRÈS de l'UPDATE

id_client	nom	email
41	Anna	anna·ex
42	Pere	vell·ex
43	Maria	maria·ex

UPDATE clients SET nom = 'Pere' WHERE id_client = 42;

Sintaxi bàsica

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Actualitzar una columna d'una fila concreta
UPDATE clients
SET email = 'nou.email@example.com'
WHERE id_client = 42;

-- Actualitzar múltiples columnes alhora
UPDATE clients
SET
    nom = 'Joan Antoni',
    cognoms = 'Garcia i Puig',
    telefon = '972999888',
    data_modificacio = CURRENT_TIMESTAMP
WHERE dni = '12345678A';

-- Actualitzar basant-se en una expressió calculada
UPDATE productes
SET preu = preu * 1.05          -- Pujada del 5%
WHERE id_categoria = 3;

-- Actualitzar amb subconsulta
UPDATE productes
SET preu = preu * 0.90          -- 10% de descompte
WHERE id_producte IN (
    SELECT id_producte FROM linies_comanda
    WHERE id_comanda IN (
        SELECT id_comanda FROM comandes
        WHERE EXTRACT(YEAR FROM data_com) = 2024
    )
);

UPDATE clients
SET email = 'nou.email@example.com'
WHERE id_client = 42;

UPDATE productes
SET preu = preu * 1.05
WHERE id_categoria = 3;

UPDATE clients
SET email = 'nou.email@example.com'
WHERE id_client = 42;

-- UPDATE amb JOIN (SQL Server i MySQL)
UPDATE p
SET p.preu = p.preu * 0.90
FROM productes p
JOIN categories c ON p.id_categoria = c.id_categoria
WHERE c.nom = 'liquidacio';

UPDATE clients
SET email = 'nou.email@example.com'
WHERE id_client = 42;

-- Actualitzar múltiples columnes
UPDATE clients
SET
    nom = 'Joan Antoni',
    cognoms = 'Garcia i Puig',
    telefon = '972999888',
    data_modificacio = CURRENT_TIMESTAMP
WHERE dni = '12345678A';

-- Actualitzar amb expressió calculada
UPDATE productes
SET preu = preu * 1.05
WHERE id_categoria = 3;

-- Actualitzar amb subconsulta
UPDATE productes
SET preu = preu * 0.90
WHERE id_producte IN (
    SELECT id_producte FROM linies_comanda
    WHERE id_comanda IN (
        SELECT id_comanda FROM comandes
        WHERE EXTRACT(YEAR FROM data_com) = 2024
    )
);

UPDATE amb JOIN

Tots els SGBD permeten actualitzar files basant-se en dades d'una altra taula:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Actualitzar el preu dels productes d'una categoria concreta
-- (usant FROM a PostgreSQL)
UPDATE productes p
SET preu = preu * 0.90
FROM categories c
WHERE p.id_categoria = c.id_categoria
  AND c.nom = 'liquidacio';

-- Multi-table UPDATE (MySQL)
UPDATE productes p
JOIN categories c ON p.id_categoria = c.id_categoria
SET p.preu = p.preu * 0.90
WHERE c.nom = 'liquidacio';

UPDATE p
SET p.preu = p.preu * 0.90
FROM productes p
JOIN categories c ON p.id_categoria = c.id_categoria
WHERE c.nom = 'liquidacio';

-- Oracle no suporta UPDATE...FROM amb JOIN directament
-- S'usa una subconsulta correlacionada o IN:
UPDATE productes
SET preu = preu * 0.90
WHERE id_categoria IN (
    SELECT id_categoria FROM categories WHERE nom = 'liquidacio'
);
-- O amb EXISTS (equivalent):
UPDATE productes p
SET preu = preu * 0.90
WHERE EXISTS (
    SELECT 1 FROM categories c
    WHERE c.id_categoria = p.id_categoria AND c.nom = 'liquidacio'
);

UPDATE sense WHERE

Un UPDATE sense clàusula WHERE modifica totes les files de la taula. En una taula de producció amb milions de registres, això pot ser catastròfic i irreversible. Sempre verifiqueu la clàusula WHERE amb un SELECT previ:

-- Primer, verifiqueu quines files afectareu:
SELECT * FROM clients WHERE actiu = FALSE;
-- Si el resultat és l'esperat, executeu l'UPDATE:
UPDATE clients SET data_baixa = CURRENT_DATE WHERE actiu = FALSE;

DELETE

La instruccio DELETE FROM elimina files d'una taula. Com l'UPDATE, sempre ha d'anar acompanyada d'un WHERE (llevat que vulgueu eliminar totes les files).

clients — ABANS

id_client	nom	actiu
41	Anna	TRUE
42	Joan	FALSE
43	Maria	TRUE

→

clients — DESPRÈS del DELETE

id_client	nom	actiu
41	Anna	TRUE
~~42~~	~~Joan~~	~~FALSE~~
43	Maria	TRUE

DELETE FROM clients WHERE id_client = 42;

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Eliminar una fila concreta
DELETE FROM clients WHERE id_client = 99;

-- Eliminar amb condició
DELETE FROM comandes
WHERE estat = 'cancel·lada'
  AND data_com < CURRENT_DATE - INTERVAL '2 years';

-- Eliminar basant-se en una altra taula (USING a PostgreSQL)
DELETE FROM linies_comanda lc
USING comandes co
WHERE lc.id_comanda = co.id_comanda
  AND co.estat = 'cancel·lada';

-- Eliminar i retornar les files eliminades (RETURNING)
DELETE FROM clients
WHERE actiu = FALSE
RETURNING id_client, nom, cognoms;

DELETE FROM clients WHERE id_client = 99;

DELETE FROM comandes
WHERE estat = 'cancel·lada'
  AND data_com < DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

-- Multi-table DELETE (MySQL)
DELETE lc
FROM linies_comanda lc
JOIN comandes co ON lc.id_comanda = co.id_comanda
WHERE co.estat = 'cancel·lada';

DELETE FROM clients WHERE id_client = 99;

-- DELETE amb OUTPUT (retorna les files eliminades)
DELETE FROM clients
OUTPUT DELETED.id_client, DELETED.nom
WHERE actiu = 0;

DELETE FROM clients WHERE id_client = 99;

DELETE FROM comandes
WHERE estat = 'cancel·lada'
  AND data_com < ADD_MONTHS(SYSDATE, -24);   -- fa 2 anys

-- Oracle no suporta DELETE...USING; s'usa IN o EXISTS:
DELETE FROM linies_comanda
WHERE id_comanda IN (
    SELECT id_comanda FROM comandes WHERE estat = 'cancel·lada'
);

-- Eliminar i retornar les files eliminades (RETURNING INTO, en PL/SQL)
DELETE FROM clients
WHERE actiu = 0
RETURNING id_client, nom INTO :v_id, :v_nom;

Restriccions de clau forana en el DELETE

Si intenteu eliminar una fila que és referenciada per una FK d'una altra taula, el comportament depèn de l'acció referencial configurada:

RESTRICT / NO ACTION (per defecte): L'operació falla amb un error.
CASCADE: S'eliminen en cascada totes les files dependents.
SET NULL: Les FK de les files dependents es posen a NULL.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Intentar eliminar un client amb comandes associades (FK RESTRICT)
DELETE FROM clients WHERE id_client = 1;
-- ERROR: update or delete on table "clients" violates foreign key constraint
-- "fk_com_cli" on table "comandes"

-- Solució: eliminar primer les comandes (i les línies en cascada)
DELETE FROM comandes WHERE id_client = 1;
DELETE FROM clients WHERE id_client = 1;

DELETE FROM clients WHERE id_client = 1;
-- ERROR 1451 (23000): Cannot delete or update a parent row:
-- a foreign key constraint fails

DELETE FROM comandes WHERE id_client = 1;
DELETE FROM clients WHERE id_client = 1;

DELETE FROM clients WHERE id_client = 1;
-- The DELETE statement conflicted with the REFERENCE constraint "fk_com_cli".

DELETE FROM comandes WHERE id_client = 1;
DELETE FROM clients WHERE id_client = 1;

DELETE FROM clients WHERE id_client = 1;
-- ORA-02292: integrity constraint violated - child record found

DELETE FROM comandes WHERE id_client = 1;
DELETE FROM clients WHERE id_client = 1;

TRUNCATE

TRUNCATE TABLE elimina totes les files d'una taula de forma molt més ràpida que DELETE FROM (sense WHERE). Útil per buidar taules de test o de càrrega temporal.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Buidar una taula
TRUNCATE TABLE linies_comanda;

-- Buidar i reiniciar la seqüència SERIAL
TRUNCATE TABLE clients RESTART IDENTITY;

-- Buidar en cascada (buida també les taules referenciades per FK)
TRUNCATE TABLE clients CASCADE;
-- Atenció: buida també comandes, linies_comanda, etc.

-- Desactivar FK per poder truncar
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE linies_comanda;
TRUNCATE TABLE comandes;
TRUNCATE TABLE clients;
SET FOREIGN_KEY_CHECKS = 1;

TRUNCATE TABLE linies_comanda;
-- SQL Server no permet TRUNCATE si hi ha FK actives apuntant a la taula

TRUNCATE TABLE linies_comanda;
-- TRUNCATE a Oracle és DDL: fa COMMIT implícit i no es pot fer ROLLBACK

-- Alliberar l'espai físic al tablespace
TRUNCATE TABLE linies_comanda DROP STORAGE;

AC0372/05/02 — Miniactivitat

RA5 · CA5.3, CA5.4

Practiqueu UPDATE i DELETE de forma segura:

Seleccioneu primer amb un SELECT els registres que voleu modificar.
Executeu un UPDATE per augmentar el preu de les instal·lacions de tipus "piscina" un 10%.
Marqueu com a "inactives" les reserves que son de fa més de 6 mesos.
Elimineu les reserves cancel·lades de fa més d'un any.
Documenteu cada operació amb el nombre de files afectades (GET DIAGNOSTICS / ROW_COUNT()).