UPDATE ... FROM: atualizacoes em massa com join e subconsulta

Um UPDATE simples fixa linhas em valores constantes. Assim que o novo valor depende de outra tabela ou de um agregado, voce recorre ao UPDATE ... FROM: a construcao que une a tabela alvo a uma fonte dentro do proprio update.

Sintaxe basica

No PostgreSQL a fonte vai no FROM e o vinculo entre as linhas vai no WHERE. Vamos copiar o pais de cada usuario para os seus pedidos (uma desnormalizacao para relatorios):

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 e a fonte e WHERE o.user_id = u.id e a condicao de join. Cada linha de orders herda o pais do seu usuario. Tire essa condicao e o resultado muda por completo, que e exatamente a armadilha da proxima secao.

A armadilha: um WHERE ausente

A condicao de join no UPDATE ... FROM vive no WHERE, nao em um ON separado. Se voce esquece, o motor cruza cada linha alvo com cada linha de origem (um produto cartesiano) e atualiza todas as linhas para o mesmo valor:

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

• Todos os pedidos recebem o pais de um usuario qualquer.
• Recuperar exige um backup ou um rollback.

Gotcha: envolva esses comandos em uma transacao e teste-os como um SELECT com o mesmo FROM/WHERE antes de confirmar. No PostgreSQL: BEGIN;, rode o UPDATE, confira SELECT count(*) e so entao COMMIT;.

Atualizar a partir de um agregado

A fonte mais comum e uma subconsulta agrupada. Vamos guardar o total de pedidos de cada usuario em uma coluna propria:

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;

Usuarios sem pedidos pagos nunca aparecem na subconsulta e mantem o valor padrao 0. Se voce tambem precisa zera-los de forma explicita, adicione um UPDATE ... WHERE id NOT IN (...) separado ou use uma fonte no estilo LEFT JOIN.

Desnormalizar campos calculados

O mesmo truque mantem os agregados quentes a mao para nao recalcula-los a cada leitura. Por exemplo, um aumento por departamento conforme o 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;

A subconsulta escolhe os departamentos com salario medio baixo e o UPDATE externo sobe o salario em 10% para todos desses departamentos.

• A desnormalizacao acelera as leituras mas exige recalculo sempre que a fonte muda.
• Mantenha esses updates na mesma transacao da escrita na origem.

MySQL: UPDATE ... JOIN

O MySQL nao tem UPDATE ... FROM. O join e escrito logo apos o UPDATE com JOIN, e o vinculo vai no 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;

A armadilha e a mesma ao contrario: um INNER JOIN sem ON, ou ON 1=1, traz o produto cartesiano de volta. O ClickHouse vai ainda mais longe: nao tem um UPDATE linha a linha comum; usa-se o assincrono ALTER TABLE ... UPDATE e, para cruzar tabelas, o usual sao os engines e o JOIN num SELECT.

Resumo: o PostgreSQL poe a fonte no FROM e o vinculo no WHERE; o MySQL poe ambos no JOIN ... ON. Uma so regra manda: sem condicao de join, voce atualiza a tabela inteira.