一、场景描述

在实际开发中，有时需要根据另一个表或复杂查询的结果来批量更新目标表中的多个字段。当简单的WHERE条件无法满足需求时，可以通过以下几种方式实现复杂的SQL更新操作。

二、实现方法

1. 使用子查询进行更新

通过子查询从关联表中获取需要更新的值，并将其赋给目标表的字段。此方法适用于数据量较小或中等规模的场景。

UPDATE target_table tgt
SET 
    client_code = (
        SELECT src.client_code
        FROM source_table src
        LEFT JOIN participant_table part ON part.user_id = src.id
        WHERE src.email = tgt.email
    ),
    user_name = (
        SELECT part.participant_name
        FROM source_table src
        LEFT JOIN participant_table part ON part.user_id = src.id
        WHERE src.email = tgt.email
    )
WHERE EXISTS (
    SELECT 1
    FROM source_table src
    LEFT JOIN participant_table part ON part.user_id = src.id
    WHERE src.email = tgt.email
);

注意：对于大规模数据更新，可以先将子查询结果存储到临时表中以提高性能。

2. 利用临时表优化更新

当数据量较大时，可以先将查询结果保存到一个临时表中，然后基于该临时表进行更新操作。这种方法可以显著提升效率。

-- 创建并填充临时表
CREATE TEMPORARY TABLE temp_data AS
SELECT 
    src.email,
    part.client_code,
    part.participant_code,
    part.participant_name
FROM source_table src
LEFT JOIN participant_table part ON part.user_id = src.id;

-- 执行更新操作
UPDATE target_table tgt
JOIN temp_data tmp ON tgt.email = tmp.email
SET 
    tgt.client_code = tmp.client_code,
    tgt.participant_code = tmp.participant_code,
    tgt.participant_name = tmp.participant_name;

-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_data;

3. 使用CTE（公共表达式）简化语法

如果数据库支持CTE（如MySQL 8.0+），可以直接使用WITH语句定义中间结果集，从而避免创建和管理临时表。

WITH intermediate_data AS (
    SELECT 
        src.email,
        part.client_code,
        part.participant_code,
        part.participant_name
    FROM source_table src
    LEFT JOIN participant_table part ON part.user_id = src.id
)
UPDATE target_table tgt
JOIN intermediate_data id ON tgt.email = id.email
SET 
    tgt.client_code = id.client_code,
    tgt.participant_code = id.participant_code,
    tgt.participant_name = id.participant_name;

三、方法对比