mysql - 在 key 更新时选择最大合并？

Question

我的 sql 查询工作正常，直到我尝试添加绝对最后一行。我怎样才能让那部分工作？

INSERT INTO posts (ssp_order, ssp_id, ssp_ss_id, ssp_c_id) 
SELECT COALESCE(MAX(ssp_order),0)+1 ,
" . $sspid . "," . $ssid . "," . $cid . "
FROM posts
WHERE ssp_ss_id = " . $ssid . "
ON DUPLICATE KEY UPDATE
ssp_status = 0,
ssp_order = SELECT COALESCE(MAX(ssp_order),0)+1 FROM posts

(请不要担心里面变量的安全性)

Answer 1

引用 select 语句中使用的表而不是使用子查询

INSERT INTO posts (ssp_order, ssp_id, ssp_ss_id, ssp_c_id) 
SELECT maxssporder ,sspid,ssid,cid from
(SELECT COALESCE(MAX(ssp_order),0)+1 as maxssporder,
" . $sspid . " as sspid," . $ssid . " as ssid," . $cid . " as cid
FROM posts p
WHERE ssp_ss_id = " . $ssid . ") q
ON DUPLICATE KEY UPDATE
ssp_status = 0,
ssp_order = q.maxssporder