MySQL删除SELECT查询结果

jues 提交于 2019/08/03 - 10:26 周六
内容

查询表内容:

MariaDB [test]> SELECT * FROM `user`;
+-------+
| name  |
+-------+
| alan  |
| jack  |
| jason |
+-------+
3 rows in set (0.00 sec)

 

删除name字段为j开头的数据(错误)
错误:ERROR 1093 (HY000): You can't specify target table 'user' for update in FROM clause 表示不能在同一条语句执行select后又update这个数据表。

MariaDB [test]> DELETE FROM `user` WHERE `name` IN (\
    -> SELECT `name` FROM `user` WHERE `name` LIKE 'j%'\
    -> );
ERROR 1093 (HY000): You can't specify target table 'user' for update in FROM clause
MariaDB [test]> 

 

删除name字段为j开头的数据(正确)

解决:在中间增加一个临时表temp(这个名字可以自定义)

MariaDB [test]> DELETE FROM `user` WHERE `name` IN (\
    -> SELECT `temp`.`name` FROM (\
    -> SELECT * FROM `user` WHERE `name` LIKE 'j%'\
    -> )\
    -> temp\
    -> );
Query OK, 2 rows affected (0.00 sec)

 

验证结果

MariaDB [test]> SELECT * FROM `user`;
+------+
| name |
+------+
| alan |
+------+
1 row in set (0.00 sec)

 

添加新评论

此字段内容将保密,不会被其他人看见。