SQL 更新 - 多列

问题描述

我想使用 Select 语句根据第二个表中的值更新表中的多个列以获得如下值:

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'

推荐答案

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

这篇关于SQL 更新 - 多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！