SQL Server中使用JOIN进行UPDATE语句操作的方法

技术背景

在数据库操作中，经常需要根据一张表的数据来更新另一张表的数据。不同的数据库管理系统（DBMS）提供了不同的语法来实现带有JOIN的UPDATE语句。了解这些语法可以帮助开发者在不同的环境中高效地完成数据更新任务。

实现步骤

ANSI/ISO标准方法

该方法理论上适用于任何SQL DBMS，但通常比特定DBMS的方法慢。

update ud 
    set assid = (
        select sale.assid 
        from sale 
        where sale.udid = ud.id
    )
where exists (
    select * 
    from sale 
    where sale.udid = ud.id
);

SQL Server方法

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

MySQL方法

update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid

PostgreSQL方法

update ud
    set assid = s.assid
from sale s 
where ud.id = s.udid;

Oracle方法

update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid

SQLite方法

update ud 
    set assid = (
        select sale.assid 
        from sale 
        where sale.udid = ud.id
    )
where RowID in (
    select RowID 
    from ud 
    where sale.udid = ud.id
);

简化的多表JOIN更新查询

UPDATE
    first_table ft
    JOIN second_table st ON st.some_id = ft.some_id
    JOIN third_table tt  ON tt.some_id = st.some_id
    .....
SET
    ft.some_column = some_value
WHERE ft.some_column = 123456 AND st.some_column = 123456

使用公共表表达式（CTE）

with cte as
(select u.assid col1 ,s.assid col2 from ud u inner join sale s on u.id = s.udid)
update cte set col1=col2

MySQL性能优化示例

UPDATE users User
    INNER JOIN
        blacklist_users BlacklistUser
        ON
        (
            User.username = BlacklistUser.account_ref
            OR
            User.email = BlacklistedUser.account_ref
            OR
            User.phone_number = BlacklistUser.account_ref
            AND
            User.is_active = 1
            AND
            BlacklistUser.has_run = 0
        )
    SET
        User.is_active = 0,
        BlacklistUser.has_run = 1;

最佳实践

• 在MySQL中，将所有条件放在ON表达式中可以获得更好的性能，避免使用WHERE子句。
• 根据不同的DBMS选择合适的语法，以提高执行效率。

常见问题

• 兼容性问题：不同的DBMS对UPDATE JOIN语法的支持不同，需要根据实际使用的DBMS选择合适的语法。
• 性能问题：某些通用的ANSI/ISO方法可能会导致性能下降，应优先使用特定DBMS的优化语法。