mysql delete的一个问题

最新推荐文章于 2023-05-15 14:41:45 发布

原创最新推荐文章于 2023-05-15 14:41:45 发布 · 1.4k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#delete #mysql #sqlserver #google #query #table

mysql> select * from a;
+------+-------+
| id   | name |
+------+-------+
|    1 | test1 |
|    2 | test2 |
|    3 | test3 |
|    4 | test3 |
|    5 | test3 |
+------+-------+
5 rows in set (0.00 sec)

对这样的一个表，需要删除 name相同的记录。id没有要求，但我这里只留下最小的id。

mysql> delete from a where id not in (select min(id) as id from a group by name);
ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause

google查询结构，原来mysql不支持这样的同时有select跟delete/update同一个表的操作。不过可以变通一下：

mysql> delete from a where id not in (select * from (select min(id) as id from a group by name) b where a.id=b.id);
Query OK, 2 rows affected (0.01 sec)

当然这样的问题在sqlserver上没有了。

delete from #a where id not in (select min(id) from #aa group by name)

这样完全没有问题。