如何通过关联去重查询更新MySQL的uq_active_preset表？

问题背景 #

你拥有hf3数据库，包含5张表：

• active_preset：id、preset_id
• preset：id、birja_id、trend_id、fractal、interval_up
• birja：id、name
• trend：id、name
• uq_active_preset：id、birja、trend、fractal、interval_up

需要将active_preset中的去重记录同步到uq_active_preset，已写出正确的去重查询，但尝试的UPDATE语句报1064语法错误。

错误原因分析 #

你的原UPDATE语句有两个问题：

1. SET子句最后一个字段后多了冗余逗号
2. 未指定uq_active_preset与子查询结果的关联条件，MySQL无法匹配待更新的行

针对不同需求的正确语句 #

场景1：完全替换uq_active_preset数据（清空后插入去重记录） #

如果需要用最新的去重数据覆盖目标表内容，直接清空后插入：

-- 清空目标表（TRUNCATE比DELETE更高效，会重置自增ID）
TRUNCATE TABLE hf3.uq_active_preset;

-- 插入去重后的记录
INSERT INTO hf3.uq_active_preset (birja, fractal, trend, interval_up)
SELECT 
    b.name AS birja, p.fractal AS fractal, tre.name AS trend, p.interval_up AS interval_up  
FROM hf3.active_preset AS ap
INNER JOIN hf3.preset AS p ON p.id = ap.preset_id
INNER JOIN hf3.birja AS b ON b.id = p.birja_id
INNER JOIN hf3.trend AS tre ON tre.id = p.trend_id
GROUP BY b.name, p.fractal, tre.name, p.interval_up;

注：原查询的HAVING COUNT(*) >=1可省略，GROUP BY后的结果默认都是存在至少一次的记录

场景2：同步数据（更新现有记录+插入新记录） #

如果目标表已有数据，需要保留原有记录，仅更新匹配项、插入新的去重记录，需先给uq_active_preset设置联合唯一索引：

-- 创建联合唯一索引，用于判断记录是否已存在
ALTER TABLE hf3.uq_active_preset ADD UNIQUE INDEX idx_unique_preset (birja, fractal, trend, interval_up);

-- 插入并同步数据
INSERT INTO hf3.uq_active_preset (birja, fractal, trend, interval_up)
SELECT 
    b.name AS birja, p.fractal AS fractal, tre.name AS trend, p.interval_up AS interval_up  
FROM hf3.active_preset AS ap
INNER JOIN hf3.preset AS p ON p.id = ap.preset_id
INNER JOIN hf3.birja AS b ON b.id = p.birja_id
INNER JOIN hf3.trend AS tre ON tre.id = p.trend_id
GROUP BY b.name, p.fractal, tre.name, p.interval_up
ON DUPLICATE KEY UPDATE
    birja = VALUES(birja),
    fractal = VALUES(fractal),
    trend = VALUES(trend),
    interval_up = VALUES(interval_up);

场景3：仅更新目标表中已存在的记录（不插入新记录） #

如果只需要更新目标表中已有的匹配记录，不需要插入新数据，修正后的UPDATE语句如下：

UPDATE hf3.uq_active_preset uap
INNER JOIN (
    SELECT 
        b.name AS birja, p.fractal AS fractal, tre.name AS trend, p.interval_up AS interval_up  
    FROM hf3.active_preset AS ap
    INNER JOIN hf3.preset AS p ON p.id = ap.preset_id
    INNER JOIN hf3.birja AS b ON b.id = p.birja_id
    INNER JOIN hf3.trend AS tre ON tre.id = p.trend_id
    GROUP BY b.name, p.fractal, tre.name, p.interval_up
) st 
-- 通过四个字段匹配待更新的行
ON uap.birja = st.birja 
AND uap.fractal = st.fractal 
AND uap.trend = st.trend 
AND uap.interval_up = st.interval_up
SET 
    uap.birja = st.birja,
    uap.fractal = st.fractal,
    uap.trend = st.trend,
    uap.interval_up = st.interval_up;

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