sql - 较短的查询方法

Question

源表和目标表都有大约40列。要更新的行数约为20。Destination表的行数约为30k。

下面是否有更好的方法(较短的查询)？

UPDATE destination_table t1
SET 
field1 = (select field1 from src_table t2 where t2.pk = t1.pk),
field2 = (select field2 from src_table t2 where t2.pk = t1.pk),
... 
field40 = (select field40 from src_table t2 where t2.pk = t1.pk),
WHERE EXISTS (select 1 from src_table t2 where t2.pk = t1.pk)

Answer 1

您可以使用如下形式:

UPDATE      dest
SET         dest.Field1 = src.Field1,
            dest.Field2 = src.Field2,
            dest.Field3 = src.Field3,
            dest.Field4 = src.Field4,
            dest.Field5 = src.Field5,
            dest.Field6 = src.Field6
FROM        destination_table dest
INNER JOIN  src_table src
ON          src.pk = dest.pk

您只需要添加其他字段即可更新。