报错信息
今天写了一个sql语句,操作提示
[Err] 1093 - You can't specify target table 'm_ship_information' for update in FROM clause[Err] 1093
语句如下
DELETE
FROM
people
WHERE
peopleName IN (
SELECT
peopleName
FROM
people
GROUP BY
peopleName
HAVING
count(peopleName) > 1
)
AND peopleId NOT IN (
SELECT
min(peopleId)
FROM
people
GROUP BY
peopleName
HAVING
count(peopleName) > 1
)
不过在Oracle中执行是没有问题的
解决方案
对子查询进行重命名处理,比如同样的效果处理如下:
DELETE
FROM
people
WHERE
peopleName IN (
select a.name from
(SELECT
peopleName name
FROM
people
GROUP BY
peopleName
HAVING
count(peopleName) > 1) a
)
AND peopleId NOT IN (
select b.id from
(SELECT
min(peopleId) id
FROM
people
GROUP BY
peopleName
HAVING
count(peopleName) > 1) b
)
解决问题。