CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Klapsius
Klapsius

Reputation: 3359

Sql server update multiple columns from another table

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

Upvotes: 15

Views: 59814

Answers (4)

Dhawal mehta
Dhawal mehta

Reputation: 21

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..!!

Upvotes: 1

Matz
Matz

Reputation: 11

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.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

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

Upvotes: 16

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

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

Upvotes: 18

Related Questions