UPDATE ... FROM: actualizaciones masivas con join y subconsulta

Un UPDATE simple fija filas a valores constantes. En cuanto el nuevo valor depende de otra tabla o de un agregado, necesitas UPDATE ... FROM: la construccion que une la tabla destino con una fuente dentro del propio update.

Sintaxis basica

En PostgreSQL la fuente va en FROM y el vinculo entre filas va en WHERE. Copiemos el pais de cada usuario en sus pedidos (una desnormalizacion para informes):

ALTER TABLE orders ADD COLUMN country text;

UPDATE orders o
SET country = u.country
FROM users u
WHERE o.user_id = u.id;

Aqui FROM users u es la fuente y WHERE o.user_id = u.id es la condicion de join. Cada fila de orders hereda el pais de su usuario. Si quitas esa condicion el resultado cambia por completo, y esa es justo la trampa de la siguiente seccion.

La trampa: un WHERE ausente

La condicion de join en UPDATE ... FROM vive en el WHERE, no en un ON aparte. Si la olvidas, el motor empareja cada fila destino con cada fila origen (un producto cartesiano) y actualiza todas las filas al mismo valor:

-- DANGER: no join condition
UPDATE orders o
SET country = u.country
FROM users u;

• Todos los pedidos reciben el pais de un usuario cualquiera.
• Recuperarse exige un backup o un rollback.

Gotcha: envuelve estas sentencias en una transaccion y pruebalas como un SELECT con el mismo FROM/WHERE antes de confirmar. En PostgreSQL: BEGIN;, ejecuta el UPDATE, revisa SELECT count(*) y solo entonces COMMIT;.

Actualizar desde un agregado

La fuente mas habitual es una subconsulta agrupada. Guardemos el total de pedidos de cada usuario en una columna propia:

ALTER TABLE users ADD COLUMN total numeric DEFAULT 0;

UPDATE users u
SET total = s.total
FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
) s
WHERE u.id = s.user_id;

Los usuarios sin pedidos pagados nunca aparecen en la subconsulta y conservan el valor por defecto 0. Si tambien necesitas ponerlos a cero de forma explicita, anade un UPDATE ... WHERE id NOT IN (...) aparte o usa una fuente al estilo LEFT JOIN.

Desnormalizar campos calculados

El mismo truco mantiene a mano los agregados frecuentes para no recalcularlos en cada lectura. Por ejemplo, un aumento por departamento segun el salario medio:

UPDATE employees e
SET salary = salary * 1.10
FROM (
    SELECT dept
    FROM employees
    GROUP BY dept
    HAVING AVG(salary) < 50000
) low
WHERE e.dept = low.dept;

La subconsulta elige los departamentos con salario medio bajo y el UPDATE externo sube el sueldo un 10% a todos los de esos departamentos.

• La desnormalizacion acelera las lecturas pero exige recalcular cuando cambia la fuente.
• Manten esos updates en la misma transaccion que la escritura del origen.

MySQL: UPDATE ... JOIN

MySQL no tiene UPDATE ... FROM. El join se escribe justo despues de UPDATE con JOIN, y el vinculo va en ON:

UPDATE users u
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
) s ON u.id = s.user_id
SET u.total = s.total;

La trampa es la misma a la inversa: un INNER JOIN sin ON, o ON 1=1, devuelve el producto cartesiano. ClickHouse va aun mas lejos: no tiene un UPDATE fila a fila normal; se usa el asincrono ALTER TABLE ... UPDATE y, para cruzar tablas, lo habitual son los motores y el JOIN en un SELECT.

En resumen: PostgreSQL pone la fuente en FROM y el vinculo en WHERE; MySQL pone ambos en JOIN ... ON. Una sola regla manda: sin condicion de join, actualizas toda la tabla.