如何借助临时表对比更新Table1的特定Values字段值？

问题背景 #

已创建临时表tmp_table1用于获取table2与table1的差异数据，SQL语句如下：

CREATE TABLE tmp_table1 AS
    SELECT a.Date, a.Id, b.Customer_Id, a.Name, a.Values 
    FROM table2 a, table1 b
    WHERE (a.Name like '%Red%' OR a.Name like '%Blue%') 
    AND a.Date = b.Date 
    AND a.Values != b.Values 
    AND a.Customer_Id = b.Customer_Id

需求说明 #

需要通过Customer_Id、Name、Values字段对比临时表与table1，将table1的Values字段中**仅在差异位置的=N**替换为=N#10-NOV-2022（日期为硬编码值）。

示例数据 #

• Table1原数据：Values = 'ANS1=N, ANS2=Y, ANS3=N'
• 临时表数据：Values = 'ANS1=Y, ANS2=N, ANS3=N'
• 期望更新后Table1数据：Values = 'ANS1=Y, ANS2=N#10-NOV-2022, ANS3=N'

尝试的错误SQL #

以下脚本未达到预期效果：

UPDATE table1
SET Values = REPLACE(Values, '=N', '=N#DATE')
FROM (SELECT b.Values FROM table1 a, tempTable b
        WHERE a.Customer_Id = b.Customer_Id AND a.Values != b.Values)

正确解决方案 #

核心问题是要精准替换差异位置的=N，而非全局替换所有=N。需先拆分Values中的键值对，对比临时表对应内容后针对性替换，再拼接回完整字符串。

具体SQL实现（以Oracle为例） #

-- 拆分键值对并对比差异
WITH split_data AS (
    SELECT 
        t1.Customer_Id,
        t1.Name,
        REGEXP_SUBSTR(t1.Values, '[^,]+', 1, level) AS t1_item,
        REGEXP_SUBSTR(t2.Values, '[^,]+', 1, level) AS t2_item,
        level AS item_order
    FROM table1 t1
    JOIN tmp_table1 t2 
        ON t1.Customer_Id = t2.Customer_Id 
        AND t1.Name = t2.Name
    CONNECT BY 
        level <= REGEXP_COUNT(t1.Values, ',') + 1
        AND PRIOR t1.Customer_Id = t1.Customer_Id
        AND PRIOR t1.Name = t1.Name
        AND PRIOR SYS_GUID() IS NOT NULL
),
updated_items AS (
    SELECT 
        Customer_Id,
        Name,
        item_order,
        -- 仅在table1项为=N、临时表对应项不为=N时替换
        CASE 
            WHEN t1_item LIKE '%=N' AND t2_item NOT LIKE '%=N' 
            THEN REPLACE(t1_item, '=N', '=N#10-NOV-2022')
            ELSE t1_item
        END AS updated_item
    FROM split_data
)
-- 拼接更新后的项并写入table1
UPDATE table1 t1
SET t1.Values = (
    SELECT LISTAGG(updated_item, ', ') WITHIN GROUP (ORDER BY item_order)
    FROM updated_items ui
    WHERE ui.Customer_Id = t1.Customer_Id 
        AND ui.Name = t1.Name
)
WHERE EXISTS (
    SELECT 1 FROM tmp_table1 t2
    WHERE t2.Customer_Id = t1.Customer_Id 
        AND t2.Name = t1.Name
);

跨数据库适配提示 #

• SQL Server：替换为STRING_SPLIT拆分、STRING_AGG聚合
• MySQL 8.0+：用JSON_TABLE拆分、GROUP_CONCAT聚合，低版本可通过循环拆分实现

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