情景:执行删除【delete】语句时候,mysql中由于语法问题,导致sql会报错,提示错误信息是You can't specify target table for update in FROM clause;大体意思是
无法指定“从”子句中更新的目标表.例如下列sql:
DELETE t
FROM
web_ri_plyedr_due t
WHERE
t.C_PLYEDRDUE_PK_ID in (
SELECT
MIN(q.C_PLYEDRDUE_PK_ID)
FROM
web_ri_plyedr_due q
WHERE
q.C_PLY_NO = '1000001010001160000000007'
DELETE t
FROM
web_ri_plyedr_due t
WHERE
t.C_PLYEDRDUE_PK_ID in (
SELECT
MIN(q.C_PLYEDRDUE_PK_ID)
FROM
web_ri_plyedr_due q
WHERE
q.C_PLY_NO = '1000001010001160000000007'
);
可以修改如下语句:
DELETE t
FROM
web_ri_plyedr_due t
WHERE
t.C_PLYEDRDUE_PK_ID =(SELECT w.id FROM(
SELECT
MIN(q.C_PLYEDRDUE_PK_ID) AS id ##--给字段起一个别名
FROM
web_ri_plyedr_due q
WHERE
q.C_PLY_NO = '1000001010001160000000007'
) w );
也可以理解为将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql,mssql和oracle不会出现此问题。