You can't specify target table '数据表' for update in FROM clause
不能在FROM子句中为更新指定目标表
2张车票号码相同重复,找出距今日期较远的那张票号,为其增加 -1 标识。
SELECT * from tbl_zw_sell_ticket WHERE ticket_no = '00102514';
SELECT * FROM checkticket_record WHERE WEB_ORDER_ID ='4a21df1de4c411edbfc52d7671957d4e'
想要使用sql脚本快速解决此问题
思路一
UPDATE
tbl_zw_sell_ticket
SET
ticket_no = CONCAT(ticket_no, '-1')
WHERE
ticket_no = '00102514'
AND DATEDIFF(NOW(), leave_date) = (
SELECT
MAX(DATEDIFF(NOW(), leave_date))
FROM
tbl_zw_sell_ticket
WHERE
ticket_no ='00102514'
);
系统报错:You can’t specify target table ‘tbl_zw_sell_ticket’ for update in FROM clause
思路二
UPDATE
tbl_zw_sell_ticket
SET
ticket_no = CONCAT(ticket_no, '-1')
WHERE
(
SELECT
id
from
tbl_zw_sell_ticket
WHERE
ticket_no ='00102514'
order by
leave_date asc
limit 1)
系统继续报错:You can’t specify target table ‘tbl_zw_sell_ticket’ for update in FROM clause
思路三 (可以解决这个问题)
UPDATE
tbl_zw_sell_ticket
SET ticket_no = CONCAT(ticket_no, '-1')
WHERE id = (select tb.id
from (SELECT id
from tbl_zw_sell_ticket
WHERE ticket_no = '00102514'
order by leave_date asc
limit 1) tb)
原因:不能同时select同一表中的数据再进行update该表
解决:通过中间表进行更新,通过查询后的数据生成表tb,再引用表tb的数据进行更新
思路四 (优化)
UPDATE tbl_zw_sell_ticket
SET ticket_no = CONCAT(ticket_no, '-1')
WHERE ticket_no = '00102514'
ORDER BY leave_date ASC
LIMIT 1;
更快速简洁的处理该问题