1.背景:
现在有个需求,就是想删除一个查询结果大一1的全部数据
2.问题分析:
1.查询结果大于1的sql语句
SELECT r.sku FROM t_promotion_product_state r GROUP BY r.sku HAVING count( r.sku ) > 1
为了看清楚结果,添加数量值
SELECT r.sku,count(r.sku) FROM t_promotion_product_state r GROUP BY r.sku HAVING count( r.sku ) > 1
2.根据上一步查询出来的结果,做删除动作
错误写法:
DELETE from t_promotion_product_state where sku in (SELECT r.sku FROM t_promotion_product_state r GROUP BY r.sku HAVING count( r.sku ) > 1)
直接查询出来,拼接到条件里面,做删除,报错
报错
1093 - You can't specify target table 't_promotion_product_state' for update in FROM clause
错误码意思:
错误:1093 SQLSTATE: HY000 (ER_UPDATE_TABLE_USED)
意思:不能在FROM子句中制定要更新的目标表’%s’。
翻译下意思:
不能在同一个sql语句中,先select同一个表的某些值,然后再update这个表。
3.解决方案:
select的结果再通过一个中间表select多一次,就可以避免这个错误
修改sql如下:
DELETE from t_promotion_product_state where sku in (select * from ((SELECT r.sku FROM t_promotion_product_state r GROUP BY r.sku HAVING count( r.sku ) > 1)t))
执行成功