Update a table using JOIN in SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

Try   UPDATE table1 SET CalculatedColumn     SELECT  Calculated Column                            FROM table2                           WHERE table1 commonfield    common field   WHERE  BatchNO    110

User · Answer

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   EX   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

User · Answer

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

User · Answer

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    More alternatives here

User · Answer

I find it useful to turn an UPDATE into a SELECT to get the rows I want to update as a test before updating  If I can select the exact rows I want  I can update just those rows I want to update   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

User · Answer

Try it like this      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

User · Answer

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

User · Answer

Aaron s approach above worked perfectly for me  My update statement was slightly different because I needed to join based on two fields concatenated in one table to match a field in another table    --update clients table cell field from custom table containing mobile numbers  update clients set cell   m Phone from clients as c inner join  dbo   COSStaffMobileNumbers  as m  on c Last Name   c First Name   m Name

User · Answer

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  - see here and here for more details    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

[sql-server] Update a table using JOIN in SQL Server?

Examples related to sql-server

Examples related to tsql

Examples related to join

Examples related to sql-update