SQL Server中从SELECT进行UPDATE的方法

技术背景

在SQL Server数据库开发中，经常会遇到需要根据一个表的数据来更新另一个表的情况。通过SELECT语句从一个或多个表中提取数据，然后使用UPDATE语句将这些数据更新到目标表中，是一种常见的操作需求。

实现步骤

1. 使用INNER JOIN进行UPDATE

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

此代码将Some_Table（别名Table_A）与Other_Table（别名Table_B）通过id列进行内连接，当Table_A的col3列值为cool时，将Table_B的col1和col2列的值更新到Table_A的对应列。

2. 使用MERGE语句（SQL Server 2008及更高版本）

MERGE INTO YourTable T
    USING other_table S 
        ON T.id = S.id
        AND S.tsql = 'cool'
WHEN MATCHED THEN
    UPDATE 
        SET col1 = S.col1, 
            col2 = S.col2;

MERGE语句可以根据源表和目标表的匹配情况进行更新、插入或删除操作。上述代码在源表other_table和目标表YourTable的id列匹配且S.tsql为cool时，更新目标表的col1和col2列。

3. 使用公共表表达式（CTE）

;WITH CTE
     AS (SELECT col1,col2,id
         FROM   other_table
         WHERE  sql = 'cool')
UPDATE A
SET    A.col1 = B.col1,
       A.col2 = B.col2
FROM   table A
       INNER JOIN cte B
               ON A.id = B.id

通过CTE将other_table中sql为cool的数据提取出来，然后与目标表table进行连接，更新目标表的col1和col2列。

4. 使用派生表

UPDATE x
SET    x.col1 = x.newCol1,
       x.col2 = x.newCol2
FROM   (SELECT t.col1,
               t2.col1 AS newCol1,
               t.col2,
               t2.col2 AS newCol2
        FROM   [table] t
               JOIN other_table t2
                 ON t.ID = t2.ID) x

派生表是在FROM子句中使用的子查询，上述代码通过派生表获取table和other_table连接后的结果，然后更新目标表的列。

最佳实践

• 使用WHERE子句：在UPDATE语句中添加WHERE子句，避免更新不必要的行，从而减少索引重新计算和触发器触发的可能性。
• 使用CTE提高可读性：当查询逻辑较复杂时，使用CTE可以将查询逻辑分解，使代码更易于理解和维护。
• 使用MERGE处理多种操作：如果需要同时处理更新、插入或删除操作，建议使用MERGE语句。

常见问题

• 更新不确定性：如果源表在一对多连接的多端，UPDATE操作使用哪个匹配记录是不确定的。MERGE语句可以避免这个问题，若尝试多次更新同一行，它会抛出错误。
• 性能问题：在大型表上进行更新操作时，可能会导致性能下降。可以通过创建合适的索引来提高查询性能。
• 语法差异：不同的数据库系统（如MySQL和SQL Server）在UPDATE和SELECT语句的语法上可能存在差异，需要注意。例如，MySQL的UPDATE语句语法与SQL Server有所不同：

UPDATE Table1
INNER JOIN Table2
ON Table1.id = Table2.id
SET Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2