Mysql delete by primary key with select(error 1093)

Question

I want to delete a record from mysql by primary key,that is account_recharge_detail_id. when i use the following scripts,that works: SELECT account_recharge_detail_id FROM account_recharge_detail where operator_name='fanny-test'; (it returns 101) DELETE from account_recharge_detail where account_recharge_detail_id=101;

but when i join the two together: DELETE FROM account_recharge_detail where account_recharge_detail_id in ( SELECT A.account_recharge_detail_id FROM account_recharge_detail as A where A.operator_name='fanny-test')

it returns :Error Code: 1093. You can't specify target table 'account_recharge_detail' for update in FROM clause

I want to make it works in one sentence,How can I solve it?

yaoweijq yaoweijq · Accepted Answer · 2018-03-02T01:21:25

i think you could use delete from account_recharge_detail where operator_name='fanny-test'; as account_recharge_detail_id is primary key it can not be null

Mysql delete by primary key with select(error 1093)

1 Answers