使用CASE函数填充astro表star_name列时返回NULL的问题排查

我正在操作名为astro的表，已新增star_name列，希望通过CASE函数根据star_type（整数类型）的取值，将对应的星型名称填充到star_name列中。执行以下INSERT语句后提示成功，但star_name列全为NULL，请问问题出在哪里？

当前代码：

INSERT INTO astro(star_name)
SELECT 
CASE star_type
    WHEN 0 THEN 'Brown'
    WHEN 1 THEN 'Red_Dwarf'
    WHEN 2 THEN 'White_Dwarf'
    WHEN 3 THEN 'Main_Sequence'
    WHEN 4 THEN 'Supergiant'
    WHEN 5 THEN 'Hellagiant'
    ELSE 'Basic'
END
FROM astro

问题原因 #

你误用了INSERT语句——INSERT的作用是向表中添加新行，而非修改已有行的数据。执行这段代码后，表中会新增与原表行数相同的新行，这些新行仅star_name字段被赋值（来自原表的CASE计算结果），但原有行的star_name字段完全没被修改。如果后续查询的是原有行，自然会看到star_name全为NULL；如果查询所有行，会发现新行的star_name是有值的，而旧行仍是NULL。

解决办法 #

需要使用UPDATE语句来修改已有行的star_name字段，正确代码如下：

UPDATE astro
SET star_name = CASE star_type
    WHEN 0 THEN 'Brown'
    WHEN 1 THEN 'Red_Dwarf'
    WHEN 2 THEN 'White_Dwarf'
    WHEN 3 THEN 'Main_Sequence'
    WHEN 4 THEN 'Supergiant'
    WHEN 5 THEN 'Hellagiant'
    ELSE 'Basic'
END;

UPDATE语句会遍历表中每一行，根据当前行的star_type值，通过CASE计算出对应的星型名称，赋值给该行的star_name字段，这样就能实现原有行的star_name列被正确填充的需求。

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