mysql - 在 MySQL 中合并两个 UPDATE 语句

Question

有没有办法组合以下两个语句，以便我只写 UPDATE 表一次？表的名称是 STAT_SEP。

UPDATE STAT_SEP LEFT JOIN calendar on STAT_SEP.`Period` = calendar.`Unique_Period`
SET
STAT_SEP.First_Date = calendar.First_Date;

UPDATE STAT_SEP
, (SELECT First_Date AS earliest_date FROM STAT_SEP ORDER BY First_Date ASC LIMIT 1) fancy_alias
SET `Index` = TIMESTAMPDIFF(MONTH, earliest_date, First_Date);

Answer 1

您可以计算子查询中的最早日期，然后将其用于更新:

UPDATE STAT_SEP s LEFT JOIN
       Calendar c
       ON s.`Period` = c.`Unique_Period` CROSS JOIN
       (SELECT MIN(c2.First_Date) as earliest_date
        FROM STAT_SEP s2 JOIN
             Calendar c2
             ON s2.Period = c.unique_period
       ) c2
    SET s.First_Date = c.First_Date,
        s.Index = TIMESTAMPDIFF(MONTH, c2.earliest_date, c.First_Date);