我想使用Select语句根据第二个表中的值更新表中的多个列,以获取如下值：
UPDATE tbl1
SET (col1,col2,col3) = (SELECT colA,colB,colC
FROM tbl2
WHERE tbl2.id = 'someid')
WHERE tbl1.id = 'differentid'
但是,似乎可以“设置”多个列名称 – 有替代方法,而不是为每列编写单独的更新语句？
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'
