如何基于另一表内容创建并填充MySQL表的Owned列？

问题分析 #

你需要为Properties表的Owned列赋值：当Properties.id出现在Properties_Owned.Prop_id中时设为"Yes"，否则设为"No"。你的CASE语句存在语法错误，主要问题包括：

• 错误使用IS IN（正确写法是IN，或更高效的EXISTS）
• CASE表达式未嵌入到合法的SQL语句（如UPDATE或SELECT）中
• CASE分支中错误使用赋值语法，CASE应直接返回目标值

解决方案1：一次性更新Owned列（静态赋值） #

如果需要永久更新Properties表的Owned列，使用带EXISTS子查询的UPDATE语句：

UPDATE Properties p
SET Owned = CASE
    WHEN EXISTS (SELECT 1 FROM Properties_Owned po WHERE po.Prop_id = p.id)
    THEN 'Yes'
    ELSE 'No'
END;

说明：

• 该语句遍历Properties的每一行，通过EXISTS检查当前行的id是否存在于Properties_Owned的Prop_id中
• 相比IN子查询，EXISTS在处理大表时性能更优，因为找到匹配项后会立即停止检索

解决方案2：查询时动态生成Owned值（避免静态维护） #

如果Properties_Owned的数据会频繁变化，静态的Owned列可能出现数据不一致。此时可以在查询时动态计算该值：

SELECT 
    p.id,
    p.Location,
    p.Colour,
    p.Cost_And_Rent,
    CASE
        WHEN EXISTS (SELECT 1 FROM Properties_Owned po WHERE po.Prop_id = p.id)
        THEN 'Yes'
        ELSE 'No'
    END AS Owned
FROM Properties p;

说明：每次查询都会实时关联Properties_Owned获取最新的归属状态，无需手动维护Owned列

验证示例数据 #

根据你提供的Properties_Owned数据（Prop_id包含1、2、3、4、5），执行上述语句后：

• Properties表中id为1、2、3、4、5的行，Owned列值为"Yes"
• 其余行Owned列值为"No"

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