如何基于子表中行的存在性更新主表？

主表fruits与子表fruits_sub通过fruits_id关联，规则如下：

• 若子表中某fruits_id存在对应enum值（如enum=0），主表对应枚举列（如enum_0_col）需设为1
• 若子表中无该fruits_id的对应enum值，主表对应枚举列需设为0

当前主表数据与子表不符，尝试的排查SQL出现语法错误，需解决主表更新及排查问题。

表结构 #

fruits表 #

fruits_sub表 #

错误语句分析 #

你尝试的SQL存在核心问题：

1. CASE是返回单值的表达式，不能直接用于分支执行SELECT查询
2. 子查询未关联主表的fruits_id，导致EXISTS判断的是全表是否存在enum=0的记录，而非针对单个fruits_id做判断
3. 跨表引用时未建立正确关联，外部查询无法直接引用子表的fruits_sub.fruits_id

解决方案 #

1. 排查不匹配的记录 #

排查enum_0_col不匹配的记录

SELECT f.*
FROM fruits f
LEFT JOIN (
    SELECT DISTINCT fruits_id 
    FROM fruits_sub 
    WHERE enum = 0
) s0 ON f.fruits_id = s0.fruits_id
WHERE (s0.fruits_id IS NOT NULL AND f.enum_0_col != 1)
   OR (s0.fruits_id IS NULL AND f.enum_0_col != 0);

排查enum_1_col不匹配的记录

SELECT f.*
FROM fruits f
LEFT JOIN (
    SELECT DISTINCT fruits_id 
    FROM fruits_sub 
    WHERE enum = 1
) s1 ON f.fruits_id = s1.fruits_id
WHERE (s1.fruits_id IS NOT NULL AND f.enum_1_col != 1)
   OR (s1.fruits_id IS NULL AND f.enum_1_col != 0);

2. 批量更新主表数据 #

直接根据子表的存在性修正主表枚举列，一次性完成所有更新：

UPDATE fruits f
LEFT JOIN (
    SELECT DISTINCT fruits_id 
    FROM fruits_sub 
    WHERE enum = 0
) s0 ON f.fruits_id = s0.fruits_id
LEFT JOIN (
    SELECT DISTINCT fruits_id 
    FROM fruits_sub 
    WHERE enum = 1
) s1 ON f.fruits_id = s1.fruits_id
SET 
    f.enum_0_col = CASE WHEN s0.fruits_id IS NOT NULL THEN 1 ELSE 0 END,
    f.enum_1_col = CASE WHEN s1.fruits_id IS NOT NULL THEN 1 ELSE 0 END;

语句说明 #

• 用LEFT JOIN关联子表去重后的fruits_id（只需判断存在性，无需重复记录）
• 通过CASE语句根据关联结果设置枚举值：子表有对应记录则设1，否则设0
• 一次性更新两个枚举列，提升执行效率

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