如何在新MySQL迁移文件中正确更新CASE语句逻辑？

问题背景 #

我有一个用于向consumption_report_log_entry表插入数据的MySQL迁移脚本：

INSERT INTO consumption_report_log_entry
SELECT
unhex(md5(uuid())),
cr.id AS consumption_report_id,
unit_id,
u.name AS unit_name,
cru.tenancy_id AS tenancy_id,
l.name AS tenancy_name,
CASE
    WHEN cr.collection_date >= t.closed_date THEN 'CLOSED'
    WHEN cr.collection_date > t.rent_end_date THEN 'POSTTERMINATION'
    WHEN cr.collection_date >= t.termination_date THEN 'TERMINATED'
    WHEN cr.collection_date >= t.rent_start_date THEN 'ACTIVE'
    ELSE 'PENDING'
END AS tenancy_status,
tenancy_number,
rent_start_date,
rent_end_date,
l.email,
current_aconto,
new_aconto,
paid_in_advance,
actual_to_be_paid,
difference_to_be_paid,
cr.collection_date
FROM consumption_report cr
LEFT JOIN consumption_report_unit cru ON cru.consumption_report_id = cr.id
LEFT JOIN unit u ON cru.unit_id = u.id
LEFT JOIN tenancy t ON cru.tenancy_id = t.id
LEFT JOIN tenancy_tenant tt ON tt.tenancy_id = t.id
LEFT JOIN legal_entity l ON tt.tenant_id = l.id;

现在需要修改tenancy_status字段的CASE分支和ELSE语句，我平时修改字段的写法是：

UPDATE table_name
SET column1 = value1
WHERE condition;

但不确定更新这种依赖多表计算的CASE逻辑的正确方式，想问我写的下面这段脚本是否正确？

UPDATE consumption_report_log_entry
SET tenancy_status = CASE
WHEN cr.collection_date >= t.closed_date THEN 'CLOSED'
WHEN cr.collection_date >= t.rent_end_date THEN 'POSTTERMINATION'
WHEN cr.collection_date >= t.rent_start_date THEN 'ACTIVE'
ELSE 'EMPTY' END

回答 #

你写的这段UPDATE语句不正确，核心问题是：

• 直接引用了cr、t等原关联表的别名，但consumption_report_log_entry表本身没有这些字段，也没有在UPDATE中关联这些表，MySQL无法识别这些别名。
• tenancy_status是通过关联多表计算得出的，更新时必须重新关联这些原始表来获取计算所需的数据。

正确写法 #

需要在UPDATE语句中关联所有计算所需的表，重新计算tenancy_status的值：

UPDATE consumption_report_log_entry cre
JOIN consumption_report cr ON cre.consumption_report_id = cr.id
JOIN consumption_report_unit cru ON cru.consumption_report_id = cr.id
JOIN tenancy t ON cru.tenancy_id = t.id
SET cre.tenancy_status = CASE
    WHEN cr.collection_date >= t.closed_date THEN 'CLOSED'
    WHEN cr.collection_date >= t.rent_end_date THEN 'POSTTERMINATION'
    WHEN cr.collection_date >= t.rent_start_date THEN 'ACTIVE'
    ELSE 'EMPTY'
END;

额外说明 #

1. 保留LEFT JOIN逻辑：如果要保留原INSERT中的LEFT JOIN（避免因关联不上过滤掉数据），可以替换成LEFT JOIN，同时处理NULL值：

UPDATE consumption_report_log_entry cre
LEFT JOIN consumption_report cr ON cre.consumption_report_id = cr.id
LEFT JOIN consumption_report_unit cru ON cru.consumption_report_id = cr.id
LEFT JOIN tenancy t ON cru.tenancy_id = t.id
SET cre.tenancy_status = CASE
    WHEN cr.collection_date IS NULL OR t.closed_date IS NULL THEN 'EMPTY'
    WHEN cr.collection_date >= t.closed_date THEN 'CLOSED'
    WHEN cr.collection_date >= t.rent_end_date THEN 'POSTTERMINATION'
    WHEN cr.collection_date >= t.rent_start_date THEN 'ACTIVE'
    ELSE 'EMPTY'
END;

2. 添加过滤条件：如果只需要更新特定范围的数据，可在末尾加WHERE子句：

WHERE cr.collection_date BETWEEN '2023-01-01' AND '2023-12-31';

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