sql-server - two - update with join sql server
Update a table using JOIN in SQL Server? (7)
Another approach would be to use 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 is part of the SQL Standard
-Also I'm pretty sure inner join updates are non deterministic.. Similar question here where the answer talks about that http://ask.sqlservercentral.com/questions/19089/updating-two-tables-using-single-query.html
I want to update a column in a table making a join on other table e.g.:
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'
But it is complaining :
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'a'.
What is wrong here?
Answer given above by Aaron is perfect:
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';
Just want to add why this problem occurs in SQL Server when we try to use alias of a table while updating that table, below mention syntax will always give error:
update tableName t set t.name = 'books new' where t.id = 1
case can be any if you are updating a single table or updating while using join.
Although above query will work fine in PL/SQL but not in SQL Server.
Correct way to update a table while using table alias in SQL Server is:
update t set t.name = 'books new' from tableName t where t.id = 1
Hope it will help everybody why error came here.
I had the same issue.. and you don't need to add a physical column.. cuz now you will have to maintain it.. what you can do is add a generic column in the select query:
select tb1.col1, tb1.col2, tb1.col3 , ( select 'Match' from table2 as tbl2 where tbl1.col1 = tbl2.col1 and tab1.col2 = tbl2.col2 ) from myTable as tbl1
Seems like SQL Server 2012 can handle the old update syntax of Teradata too:
UPDATE a SET a.CalculatedColumn= b.[Calculated Column] FROM table1 a, table2 b WHERE b.[common field]= a.commonfield AND a.BatchNO = '110'
If I remember correctly, 2008R2 was giving error when I tried similar query.
UPDATE table1 SET CalculatedColumn = ( SELECT [Calculated Column] FROM table2 WHERE table1.commonfield = [common field]) WHERE BatchNO = '110'
You don't quite have SQL Server's proprietary
UPDATE FROM syntax down. Also not sure why you needed to join on the
CommonField and also filter on it afterward. Try this:
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';
If you're doing something really silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression):
;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];
The reason this is really silly, is that you're going to have to re-run this entire update every single time any row in
table2 changes. A
SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.
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";