翻译：MySQL不能指定更新的目标表在FROM子句
源SQL语句：
|
1
2
3
|
SQL>
delete
from
t_official_sys_user
where
USER_NAME
IN
(
SELECT
USER_NAME
FROM
t_official_sys_user b
group
by
b.`USER_NAME`
having
count
(1) > 1)
|
|
1
2
3
4
5
|
SQL>
delete
from
t_official_sys_user
where
USER_NAME
IN
(
SELECT
USER_NAME
FROM
t_official_sys_user b
group
by
b.`USER_NAME`
having
count
(1) > 1)
[Err] 1093 - You can
't specify target table '
t_official_sys_user'
for
update
in
FROM
clause
|
解决代码如下：
|
1
2
|
SQL>
from
t_official_sys_user
where
USER_NAME
IN
(
select
USER_NAME
from
(
SELECT
USER_NAME
FROM
t_official_sys_user b
group
by
b.`USER_NAME`
having
count
(1) > 1)
as
temtable );
|
分析：
先把要删除的目标放到一张临时表再把要删除的条件指定到这张临时表即可。
本文转自crazy_charles 51CTO博客，原文链接：http://blog.51cto.com/douya/1841196，如需转载请自行联系原作者
