15

I have read lots of post about how to update multiple columns but still can't find right answer.

I have one table and I would like update this table from another table.

Update table1 
set (a,b,c,d,e,f,g,h,i,j,k)=(t2.a,t2.b,t2.c,t2.d,t2.e,t2.f,t2.g,t2.h,t2.i,t2.j,t2.k)
from 
(
  SELECT ..... with join ... where .... 

) t2
    where table1.id=table2.id

If I running only select statement (between brackets) then script return values but not working with update

MUHAMMAD TASADDUQ ALI
  • 387
  • 2
  • 14
Klapsius
  • 3,273
  • 6
  • 33
  • 56

4 Answers4

18

TSQL does not support row-value constructor. Use this instead:

UPDATE table1 
SET a = t2.a,
    b = t2.b,
    (...)
FROM 
(
SELECT ..... with join ... WHERE .... 
) t2
WHERE table1.id = table2.id
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • how can we do the same in sqlalchemy – newuser Jul 01 '20 at 14:50
  • engine.execute( """UPDATE table1 SET a = t2.a, b = t2.b, (...) FROM ( SELECT ..... with join ... WHERE .... ) t2 WHERE table1.id = table2.id""") – Sean Nov 09 '21 at 16:27
16

You don't need to use a sub-query you can also simply do the following....

Update t1 
set t1.a  = t2.a
   ,t1.b  = t2.b
   ,t1.c  = t2.c
   ,t1.d  = t2.d
   .......
from table1 t1
JOIN table2 t2  ON t1.id = t2.id
WHERE .......
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 2
    In a recent (2014 I think) version of MS SQL Server, we tried this and it balked at the t1. designation on the target fields. Once we removed them the update worked as expected. We used Inner Join rather than just Join, so perhaps that makes a difference. So, we used "set a = t2.a, etc.". – Prof Von Lemongargle Jun 23 '17 at 19:46
1

The above solution will work only for MSSQL. In case of MySql you just need to first declare the tables

UPDATE 
      table1 t1 ,table2 t2 
set 
      t1.field=t2.field 
where 
      t1.id=t2.id;

In my case this worked..!!

Dhawal mehta
  • 21
  • 1
0

The UPDATE SET commands implicitly apply on the table specified by , and it is not possible to specify the table on the SET operation.

Edit: Specify only the column name you want to update, do not mention the table.

Matz
  • 11
  • 1