SQL Update - Multiple Columns

Question

I would like to update multiple columns in a table based on values from a second table using a Select statement to obtain the values like this:

UPDATE tbl1 
SET (col1, col2, col3) = (SELECT colA, colB, colC 
                          FROM tbl2 
                          WHERE tbl2.id = 'someid') 
WHERE tbl1.id = 'differentid'

However, it doesn't seem as though it's possible to 'SET' more than one column name - are there alternatives rather than writing separate update statements for each column?

UPDATE tbl1 
SET col1 = (SELECT colA FROM tbl2 WHERE tbl2.id = 'someid') 
WHERE tbl1.id = 'differentid'

UPDATE tbl1 
SET col2 = (SELECT colB FROM tbl2 WHERE tbl2.id = 'someid') 
WHERE tbl1.id = 'differentid'

UPDATE tbl1 
SET col3 = (SELECT colC FROM tbl2 WHERE tbl2.id = 'someid') 
WHERE tbl1.id = 'differentid'

score 41 · Accepted Answer · answered Apr 08 '13 at 14:28

41

update tbl1
set col1 = a.col1, col2 = a.col2, col3 = a.col3
from tbl2 a
where tbl1.Id = 'someid'
and a.Id = 'differentid'

answered Apr 08 '13 at 14:28

Tevo D

3,351
21
28

score 7 · Answer 2 · answered Apr 08 '13 at 14:19

7

This should work -

    Update Tbl1 
    SET 
    Col1 = B.ColA,
    Col2 = B.ColB,
    Col3 = B.ColC
    FROM
    Tbl2 B
    Where
    B.Id = 'Someid'

answered Apr 08 '13 at 14:19

Arun

969
7
17

both tables do not share the same id, as seen in the question, so can I put a where clause for b.id = 'someid' and tbl1.id = 'differentid' – Prakash Chennupati Apr 08 '13 at 14:22

score -1 · Answer 3 · answered Jan 28 '20 at 14:21

-1

You can find the answer here.

answered Jan 28 '20 at 14:21

Ahmed Eissa

1,139
1
8
14

SQL Update - Multiple Columns

3 Answers3

Linked