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.
为什么出错,求解。
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.
为什么出错,求解。
2、MYSQL语句不支持你的DELETE语法
支持
delete a from a left join .... on ... where ....
这种写法
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> 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)
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | lucy | 22 |
| 2 | lucy | 22 |
| 3 | tom | 22 |
| 4 | tom | 22 |
| 5 | tom | 22 |
| 6 | jerry | 22 |
| 7 | scott | 22 |
+----+-------+------+
7 rows in set (0.00 sec)mysql> delete u from user u left join (
-> select id from user
-> group by name,age
-> ) v on u.id=v.id
-> where v.id is null;
Query OK, 3 rows affected (0.01 sec)mysql> select * from user;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | lucy | 22 |
| 3 | tom | 22 |
| 6 | jerry | 22 |
| 7 | scott | 22 |
+----+-------+------+
4 rows in set (0.00 sec)mysql>
This is a nice idea,aonther method,you could use a new name for the table.