高手说说这两个sql的区别

create table user(
id int auto_increment primary key,
name varchar(20),
age int  
);
insert into user values(1,'lucy',22);
insert into user values(2,'lucy',22);
insert into user values(3,'tom',22);
insert into user values(4,'tom',22);
insert into user values(5,'tom',22);
insert into user values(6,'jerry',22);
insert into user values(7,'scott',22);

delete from user where id not in
(
select id from user
group by name,age
)
;

查询：
select * from user where id not in
(
select id from user
group by name,age
)
;
执行成功，能找到不重复的行。


删除：
delete from user where id not in
(
select id from user
group by name,age
)
;
出错了。错误为：you can't specify target table 'user' for update in FROM clause.
为什么出错，求解。

1、SQL语句不是标准的SQL语句；
2、MYSQL语句不支持你的DELETE语法
支持
delete a from a left join .... on ... where ....
这种写法

mysql中不能这么用。错误提示说，不能先select出同一表中的某些值，再update（包括delete）这个表(在同一语句中) 

正确用法：
SQL code

mysql> create table tmp as select id from user group by name,age;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> delete from user where id not in (select id from tmp);
Query OK, 3 rows affected (0.07 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.13 sec)

语法不支持