如何在ClickHouse中通过子查询关联其他表完成数据更新？

ClickHouse 原生的ALTER UPDATE突变操作不支持直接关联其他表执行JOIN更新，可通过以下三种常用方案实现需求：

方案1：ALTER UPDATE 配合子查询（适合小批量低频次更新） #

直接将关联逻辑封装到SET子句的标量子查询中：

ALTER TABLE Sales_Import
UPDATE AccountNumber = (
    SELECT AccountNumber
    FROM RetrieveAccountNumber
    WHERE LeadID = Sales_Import.LeadID
    LIMIT 1
)
WHERE LeadID IN (SELECT LeadID FROM RetrieveAccountNumber);

注意事项：

• 子查询必须保证单LeadID仅返回1条AccountNumber，新增LIMIT 1做兜底避免报错
• 该方案属于突变操作，会重写涉及的所有数据分区，不适合高频、超大数据量的更新场景

方案2：INSERT + SELECT 覆盖写入（适合大数据量更新） #

ClickHouse 为列存储架构，大数据量下全表/分区重写的效率通常远高于突变操作，可通过覆盖写入实现更新：

-- 1. 关联生成更新后的全量数据，写入临时表
CREATE TEMPORARY TABLE temp_Sales_Import AS
SELECT
    SI.LeadID,
    -- 匹配到则用RAN的账号，否则保留原有账号
    COALESCE(RAN.AccountNumber, SI.AccountNumber) AS AccountNumber,
    -- 其余字段原样保留
    SI.other_col1,
    SI.other_col2
FROM Sales_Import SI
LEFT JOIN RetrieveAccountNumber RAN ON SI.LeadID = RAN.LeadID;

-- 2. 清空原表（分区表可仅删除涉及的分区，效率更高）
TRUNCATE TABLE Sales_Import;

-- 3. 将更新后的全量数据写回原表
INSERT INTO Sales_Import SELECT * FROM temp_Sales_Import;

方案3：使用字典优化更新性能（关联表数据量小的场景） #

如果RetrieveAccountNumber表数据量较小，可提前构建内存字典提升查询效率：

-- 1. 创建LeadID到AccountNumber的映射字典
CREATE DICTIONARY IF NOT EXISTS dict_lead_account
(
    LeadID UInt64,
    AccountNumber String
)
PRIMARY KEY LeadID
SOURCE(CLICKHOUSE(TABLE 'RetrieveAccountNumber'))
LAYOUT(FLAT())
LIFETIME(3600);

-- 2. 使用字典完成更新
ALTER TABLE Sales_Import
UPDATE AccountNumber = dictGet('dict_lead_account', 'AccountNumber', LeadID)
WHERE dictHas('dict_lead_account', LeadID);

内容的提问来源于stack exchange，提问作者kataus