Actualice una tabla usando JOIN en SQL Server?

Quiero actualizar una columna en una tabla haciendo una combinación en otra tabla, por ejemplo:

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' 

Pero se está quejando:

Msg 170, nivel 15, estado 1, línea 2
Línea 2: syntax incorrecta cerca de ‘a’.

¿Que esta mal aquí?

No tiene la syntax patentada UPDATE FROM SQL Server. Además, no estoy seguro de por qué necesitaba unirse al CommonField y también filtrarlo después. Prueba esto:

 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'; 

Si está haciendo algo realmente tonto, como intentar constantemente establecer el valor de una columna en el agregado de otra columna (lo que infringe el principio de evitar el almacenamiento de datos redundantes), puede usar un CTE (expresión de tabla común) :

 ;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]; 

La razón por la cual esto es realmente tonto, es que tendrá que volver a ejecutar esta actualización completa cada vez que table2 cualquier fila en la table2 . Una SUM es algo que siempre se puede calcular en tiempo de ejecución y, al hacerlo, nunca tener que preocuparse de que el resultado sea obsoleto.

Pruébalo así:

 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: ¡malditos errores tipográficos!)

La respuesta dada por Aaron es perfecta:

 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'; 

Solo quiero agregar por qué ocurre este problema en SQL Server cuando tratamos de usar alias de una tabla mientras actualizamos esa tabla, a continuación mencionaremos la syntax que siempre dará un error:

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

caso puede ser cualquiera si está actualizando una sola tabla o actualizando mientras usa join.

Aunque la consulta anterior funcionará bien en PL / SQL pero no en SQL Server.

La forma correcta de actualizar una tabla al usar alias de tabla en SQL Server es:

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

Espero que ayude a todos por qué el error vino aquí.

 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 SQL Server 2012 también puede manejar la syntax de actualización anterior de Teradata:

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

Si recuerdo correctamente, 2008R2 estaba dando error cuando intenté una consulta similar.

  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' 

Más alternativas aquí: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

Me resulta útil convertir una ACTUALIZACIÓN en una SELECCIÓN para obtener las filas que deseo actualizar como prueba antes de actualizar. Si puedo seleccionar las filas exactas que quiero, puedo actualizar solo aquellas filas que deseo actualizar.

 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 

Otro enfoque sería 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 es parte del SQL Standard

-También estoy bastante seguro de que las actualizaciones de unión interna no son deterministas. Pregunta similar aquí donde la respuesta se refiere a eso http://ask.sqlservercentral.com/questions/19089/updating-two-tables-using-single-query. html

Tratar:

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