how to perform delete operation fro two mysql databases using mysql inner join

Question

I have two mysql database. I need to delete a row of table from one database by joining two data bases. I used the following query.

delete from operations
where operationid=( 
 select distinct(r.operationid) 
 from focuscareer02.careeroperations c 
 inner join fcuserreport.operations r 
 on c.careeroperation='clearresume' and c.careeroperationid=r.operationid
) and breakup='daily';

But I got the error like "you can't specify target table 'operations for update in from clause'".

dup of [SQL Delete: can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause), [Mysql: DELETE with IN subquery](http://stackoverflow.com/questions/5329944/mysql-delete-with-in-subquery), [MySQL DELETE FROM with subquery as condition](http://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition) — outis, Sep 10 '11 at 11:01
Note you don't need to [accept an answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) for unresolved questions. It's good to wait a few days after getting an answer before accepting it to allow time for a better answer to come along. Accepting answers both rewards the person who posted the answer and lets others know the question doesn't need any more attention. — outis, Sep 10 '11 at 11:04

score 0 · Answer 1 · answered Sep 10 '11 at 10:46

Use WHERE ... IN, rather than WHERE ... =:

delete from operations
where operationid IN ( 
    select distinct(r.operationid) 
    from focuscareer02.careeroperations c 
    inner join focuserreport.operations r 
        on c.careeroperation='clearresume'
        and c.careeroperationid=r.operationid
) and breakup='daily';

how to perform delete operation fro two mysql databases using mysql inner join

1 Answers1