Junção de atualização do PostgreSQL vs junção de atualização do SQL Server

Mas rodando no Postgres, a consulta é glacialmente lenta.

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id;

Não há condição de junção entre fooe t1, o implícito CROSS JOINforça um produto cartesiano, ou seja O(N²)(!) operações de atualização em vez de apenas O(N). E o resultado é um absurdo não determinista. O efeito também se torna aparente no plano de consulta: rows=338326628280em vez de rows=581621(Além disso: ambos os planos foram produzidos a partir de tabelas ligeiramente diferentes, mas isso parece irrelevante para a pergunta.)

Pode ser corrigido adicionando uma condição de junção como:

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id
WHERE  foo.id = t1.id;  -- !

Bem, tecnicamente, uma WHEREcondição, mas tudo a mesma coisa.

Mas isso é apenas colocar batom em um porco. Enquanto idé a coluna PK de cada tabela, isso é apenas adicionar ruído. Use o comando que você já encontrou:

UPDATE foo
SET    bar = t2.bar
FROM   foo2 t2
WHERE  foo.id = t2.id;

O manual aconselha para a FROMcláusula de UPDATE:

Não repita a tabela de destino como a, a from_itemmenos que você pretenda uma autojunção (nesse caso, ela deve aparecer com um alias no arquivo from_item).

E:

Quando uma FROMcláusula está presente, o que essencialmente acontece é que a tabela de destino é unida às tabelas mencionadas na from_itemlista e cada linha de saída da junção representa uma operação de atualização para a tabela de destino. Ao usar FROMvocê deve garantir que a junção produza no máximo uma linha de saída para cada linha a ser modificada. Em outras palavras, uma linha de destino não deve se unir a mais de uma linha da(s) outra(s) tabela(s). Se isso acontecer, apenas uma das linhas de junção será usada para atualizar a linha de destino, mas qual delas será usada não é previsível.

Essa auto-junção faz sentido (ou é mesmo necessária!) se você precisar LEFT [OUTER] JOINde uma(s) tabela(s) adicional(is). Infelizmente, não há nenhuma disposição no SQL para dizer "FROM LEFT"em um arquivo UPDATE. Exemplo:

• Anular coluna na atualização se a subconsulta retornar vazia

Quero abordar parte da questão de uma perspectiva do SQL Server.

No T-SQL eu faria uma atualização usando um join usando algo assim:

UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

Talvez algo assim, mas provavelmente não exatamente isso. É muito fácil acabar com uma junção cruzada acidental . A documentação do SQL Server realmente não fala sobre isso, mas deveria , porque isso atrapalha as pessoas o tempo todo.

A algebrização de UPDATE...FROMpode ser peculiar (geralmente para compatibilidade com versões anteriores), por isso é importante ser explícito e seguir as melhores práticas. Isso significa:

1. Atribuindo um alias
2. Atualizando esse alias ; e
3. Garantir que a atualização seja determinística , ou seja, cada linha de destino é atualizada a partir de no máximo uma linha de origem.

Extratos da mesma página de documentação:

Se o objeto que está sendo atualizado for o mesmo que o objeto na cláusula FROM e houver apenas uma referência ao objeto na cláusula FROM, um alias de objeto pode ou não ser especificado. Se o objeto que está sendo atualizado aparecer mais de uma vez na cláusula FROM, uma e apenas uma referência ao objeto não deve especificar um alias de tabela. Todas as outras referências ao objeto na cláusula FROM devem incluir um alias de objeto.

Tenha cuidado ao especificar a cláusula FROM para fornecer os critérios para a operação de atualização. Os resultados de uma instrução UPDATE são indefinidos se a instrução incluir uma cláusula FROM não especificada de forma que apenas um valor esteja disponível para cada ocorrência de coluna atualizada, ou seja, se a instrução UPDATE não for determinística.

e, revelando alguns dos problemas de algebrização:

Quando uma expressão de tabela comum (CTE) é o destino de uma instrução UPDATE, todas as referências ao CTE na instrução devem corresponder. Por exemplo, se um alias for atribuído ao CTE na cláusula FROM, o alias deverá ser usado para todas as outras referências ao CTE. Referências CTE não ambíguas são necessárias porque uma CTE não tem uma ID de objeto, que o SQL Server usa para reconhecer a relação implícita entre um objeto e seu alias. Sem esse relacionamento, o plano de consulta pode produzir um comportamento de junção inesperado e resultados de consulta não intencionais.

Então o exemplo T-SQL seria:

CREATE TABLE dbo.foo (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

CREATE TABLE dbo.foo2 (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

-- Update the alias
UPDATE F1
SET F1.bar = F2.bar
FROM dbo.foo AS F1
JOIN dbo.foo2 AS F2
    ON F2.id = F1.id -- deterministic;

Uma maneira de verificar se uma atualização é determinística no SQL Server é primeiro escrevê-la como um MERGE, que verifica atualizações não determinísticas em tempo de execução. Você não escreveria rotineiramente uma atualização simples como uma mesclagem por motivos de desempenho (e talvez porque a mesclagem tenha alguns problemas próprios).