Atualizar uma tabela usando JOIN no SQL Server?

Eu quero atualizar uma coluna em uma tabela fazendo uma junit em outra tabela, por exemplo:

UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO = '110' 

Mas está reclamando:

Msg 170, nível 15, estado 1, linha 2
Linha 2: syntax incorreta perto de ‘a’.

O que está errado aqui?

Você não tem a syntax proprietária UPDATE FROM SQL Server. Também não sei por que você precisou se juntar ao CommonField e também filtrá-lo depois. Tente isto:

 UPDATE t1 SET t1.CalculatedColumn = t2.[Calculated Column] FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t1.CommonField = t2.[Common Field] WHERE t1.BatchNo = '110'; 

Se você está fazendo algo realmente bobo – como constantemente tentando definir o valor de uma coluna para o agregado de outra coluna (o que viola o princípio de evitar o armazenamento de dados redundantes), você pode usar um CTE (expressão de tabela comum) :

 ;WITH t2 AS ( SELECT [key], CalculatedColumn = SUM(some_column) FROM dbo.table2 GROUP BY [key] ) UPDATE t1 SET t1.CalculatedColumn = t2.CalculatedColumn FROM dbo.table1 AS t1 INNER JOIN t2 ON t1.[key] = t2.[key]; 

A razão pela qual isso é realmente bobo, é que você terá que re-executar toda esta atualização toda vez que qualquer linha na table2 mudar. Uma SUM é algo que você sempre pode calcular em tempo de execução e, ao fazer isso, nunca precisa se preocupar que o resultado seja obsoleto.

Experimente assim:

 begin tran UPDATE a SET a.CalculatedColumn= b.[Calculated Column] FROM table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] WHERE a.BatchNO = '110' commit tran 

(editar: erros de digitação!)

Resposta dada acima por Aaron é perfeita:

 UPDATE a SET a.CalculatedColumn = b.[Calculated Column] FROM Table1 AS a INNER JOIN Table2 AS b ON a.CommonField = b.[Common Field] WHERE a.BatchNo = '110'; 

Só quero adicionar porque este problema ocorre no SQL Server quando tentamos usar o alias de uma tabela ao atualizar essa tabela, a syntax abaixo mencionada sempre dará erro:

 update tableName t set t.name = 'books new' where t.id = 1 

O caso pode ser qualquer um se você estiver atualizando uma única tabela ou atualizando enquanto estiver usando o join.

Embora a consulta acima funcione bem em PL / SQL, mas não no SQL Server.

A maneira correta de atualizar uma tabela ao usar o alias de tabela no SQL Server é:

 update t set t.name = 'books new' from tableName t where t.id = 1 

Espero que ajude a todos porque o erro chegou aqui.

 MERGE table1 T USING table2 S ON T.CommonField = S."Common Field" AND T.BatchNo = '110' WHEN MATCHED THEN UPDATE SET CalculatedColumn = S."Calculated Column"; 

Parece que o SQL Server 2012 também pode lidar com a antiga syntax de atualização do Teradata:

 UPDATE a SET a.CalculatedColumn= b.[Calculated Column] FROM table1 a, table2 b WHERE b.[common field]= a.commonfield AND a.BatchNO = '110' 

Se bem me lembro, 2008R2 estava dando erro quando tentei consulta semelhante.

  UPDATE mytable SET myfield = CASE other_field WHEN 1 THEN 'value' WHEN 2 THEN 'value' WHEN 3 THEN 'value' END From mytable Join otherTable on otherTable.id = mytable.id Where othertable.somecolumn = '1234' 

Mais alternativas aqui: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

Acho útil transformar um UPDATE em um SELECT para obter as linhas que desejo atualizar como um teste antes de atualizar. Se eu puder selecionar as linhas exatas que eu quero, posso atualizar apenas as linhas que quero atualizar.

 DECLARE @expense_report_id AS INT SET @expense_report_id = 1027 --UPDATE expense_report_detail_distribution --SET service_bill_id = 9 SELECT * FROM expense_report_detail_distribution erdd INNER JOIN expense_report_detail erd INNER JOIN expense_report er ON er.expense_report_id = erd.expense_report_id ON erdd.expense_report_detail_id = erd.expense_report_detail_id WHERE er.expense_report_id = @expense_report_id 

Outra abordagem seria usar MERGE

  ;WITH cteTable1(CalculatedColumn, CommonField) AS ( select CalculatedColumn, CommonField from Table1 Where BatchNo = '110' ) MERGE cteTable1 AS target USING (select "Calculated Column", "Common Field" FROM dbo.Table2) AS source ("Calculated Column", "Common Field") ON (target.CommonField = source."Common Field") WHEN MATCHED THEN UPDATE SET target.CalculatedColumn = source."Calculated Column"; 

-Merge faz parte do SQL Standard

-Também tenho certeza que as atualizações de junit interna não são determinísticas. Pergunta semelhante aqui, onde a resposta fala sobre isso http://ask.sqlservercentral.com/questions/19089/updating-two-tables-using-single-query. html

Experimentar:

 UPDATE table1 SET CalculatedColumn = ( SELECT [Calculated Column] FROM table2 WHERE table1.commonfield = [common field]) WHERE BatchNO = '110'