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.
为什么出错,求解。
解决方案 »
- 本地连接数据库错误
- 如何查询一个表格的数据并对其总条件进行汇总
- 客户端连不上mysql服务,但是cmd命令下可以
- 求mysql存储过程
- 插入中文报错:incorrect string value
- MYSQL有索引时插入过慢
- 如何根据查询的结果UPDATE数据??(MYSQL)
- 怎樣設置Linux底下的Mysql字元集?----100分贈送
- 小弟初用MySql,哪位大侠能介绍一下用oledb如何连接mysql啊.用特殊驱动吗?哪有?安装mysql时有什么特殊配置吗?....分可加
- Mysql数据同步
- with read only 从 oracle 转到 mysql 的问题
- 触发器创建成功,但监测时报错,不知为什么?
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.