MySQL 8使用WITH子句执行UPDATE更新的语法问题及解决方案

问题背景 #

我此前主要使用SQL Server和Oracle数据库，对MySQL的使用经验较少，需要实现的需求是：基于带WITH子句的查询结果更新指定表的两个字段。简化后的查询结构如下：

WITH p1 AS(
SELECT * FROM FIRST_TABLE
), p2 AS(
SELECT * FROM SECOND_TABLE
), p3 AS(
SELECT * FROM THIRD_TABLE
) 
SELECT * FROM p3

我尝试适配MERGE、UPDATE语法都没有成功，完整的错误查询语句如下：

WITH p1 AS(
SELECT C.produzione, C.vcf_3_1, C.cid, G.changelogid, G.modified_date AS start, G.description FROM vte_cartellinoproduzione C JOIN vte_changelog G ON C.cid = G.parent_id 
), p2 AS(
SELECT C.produzione, C.vcf_3_1, C.cid, G.changelogid, G.modified_date AS end, G.description FROM vte_cartellinoproduzione C JOIN vte_changelog G ON C.cid = G.parent_id
), p3 AS(
SELECT p1.cid, p1.vcf_3_1 AS nome, p1.produzione, DATEDIFF(end,start) AS giorni_totali, 5 * (DATEDIFF(end, start) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(start) + WEEKDAY(end) + 1, 1) AS giorni_produzione
 FROM p1 JOIN p2 ON p1.cid = p2.cid
), p4 AS(
SELECT nome, produzione, cid, MIN(giorni_produzione) AS giorni_produzione, MIN(giorni_totali) AS giorni_totali
FROM p3 WHERE giorni_produzione > 0 GROUP BY nome, produzione, cid
)
UPDATE produzionecf 
SET cf_hm2_1601 = p4.giorni_totali, cf_hm2_1602 = p4.giorni_produzione
FROM produzionecf cf 
JOIN p4
ON p4.cid = cf.cid;

执行后MySQL返回语法错误，错误信息如下：

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ') UPDATE produzionecf SET
cf_hm2_1601 = p4.giorni_totali, cf_hm2_' at line 1

之后我还尝试了另外两种UPDATE写法（仅展示UPDATE部分），均无法正常运行：

UPDATE produzionecf 
SET cf_hm2_1601 = p4.giorni_totali, cf_hm2_1602 = p4.giorni_produzione
WHERE cid = (SELECT cid FROM p4);

UPDATE produzionecf 
SET cf_hm2_1601 = p4.giorni_totali, cf_hm2_1602 = p4.giorni_produzione
WHERE cid = p4.cid

最终解决方案 #

原代码存在两个问题：一是使用了SQL Server支持但MySQL不兼容的UPDATE ... FROM语法，二是存在多余标点错误。MySQL中可以直接将公共表达式定义后接UPDATE JOIN语句实现关联更新，正确的完整写法如下：

WITH p1 AS(
SELECT C.produzione, C.vcf_3_1, C.cid, G.changelogid, G.modified_date AS start, G.description FROM vte_cartellinoproduzione C JOIN vte_changelog G ON C.cid = G.parent_id 
), p2 AS(
SELECT C.produzione, C.vcf_3_1, C.cid, G.changelogid, G.modified_date AS end, G.description FROM vte_cartellinoproduzione C JOIN vte_changelog G ON C.cid = G.parent_id
), p3 AS(
SELECT p1.cid, p1.vcf_3_1 AS nome, p1.produzione, DATEDIFF(end,start) AS giorni_totali, 5 * (DATEDIFF(end, start) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(start) + WEEKDAY(end) + 1, 1) AS giorni_produzione
 FROM p1 JOIN p2 ON p1.cid = p2.cid
), p4 AS(
SELECT nome, produzione, cid, MIN(giorni_produzione) AS giorni_produzione, MIN(giorni_totali) AS giorni_totali
FROM p3 WHERE giorni_produzione > 0 GROUP BY nome, produzione, cid
)
UPDATE produzionecf cf 
JOIN p4 ON p4.cid = cf.cid 
SET cf_hm2_1601 = p4.giorni_totali, cf_hm2_1602 = p4.giorni_produzione;

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