Error in mysql delete query

Question

delete from tx_table 
 where buyer_id in(select t.buyer_id     
                      from tx_table t                   
                      left join user_table u                  
                      on t.buyer_id=u.user_id                 
                      where u.user_id is null)

I am getting error for above query.The error is

sql error 1093:You can't specify target table 'tx_table' for update in FROM clause

Please help me

Please check out this answer http://stackoverflow.com/a/45498/4323504 — Luthando Ntsekwa, Mar 16 '15 at 07:50

score 0 · Answer 1 · answered Mar 16 '15 at 07:51

0

Try this one

DELETE FROM tx_table dlt
INNER JOIN (SELECT t.buyer_id FROM tx_table t 
LEFT JOIN user_table u ON t.buyer_id = u.user_id
WHERE u.user_id IS NULL) tmp ON dlt.buyer_id = tmp.buyer_id;

answered Mar 16 '15 at 07:51

RBB08

126
5

score 0 · Accepted Answer · answered Mar 16 '15 at 07:51

0

Try this:

DELETE FROM tx_table 
WHERE buyer_id IN (
    SELECT buyer_id  FROM (
        SELECT DISTINCT  t.buyer_id AS buyer_id FROM tx_table 
        LEFT JOIN user_table u on t.buyer_id=u.user_id where u.user_id is null
    ) AS c
)

answered Mar 16 '15 at 07:51

Rahul Tripathi

168,305
31
280
331

score 0 · Answer 3 · answered Mar 16 '15 at 07:52

Maybe the sql sentence is wrong. Do:

 $delete = delete from tx_table 
 where buyer_id in(select t.buyer_id     
                      from tx_table t                   
                      left join user_table u                  
                      on t.buyer_id=u.user_id                 
                      where u.user_id is null);
echo delete

Now take "echo" sentence and go mysql server and write paste in sql tab. If you doing this you know is a sql error.

Error in mysql delete query

3 Answers3