Error #1093 - You can't specify target table 'relProductsPrices' for update in FROM clause

Question

I'm upgrading and optimizing an old table structure. In order to properly work with replace into, I'm removing old zombie entries that interfer with the new unique key over 2 columns.

Query:

 DELETE from `relProductsPrices` where `ID` in 

  (SELECT scanA.ID from `relProductsPrices` as scanA 
             inner join `relProductsPrices` as scanB 

   where scanA.ID        < scanB.ID 
     and scanA.product   = scanB.product
     and scanA.priceName = scanB.priceName);

Error:

#1093 - You can't specify target table 'relProductsPrices' for update in FROM clause

I'm not sure how to get this into one mySQL Query properly, at this time?

I hope this question is no duplicate entry, I seemed unable to find a similar, adaptable entry. There are questions regarding this error, but I'm not having an update query here at all, and the solution most people state (create a subselect) was already done by me beforehand already.

Thanks in advance!

Answer 1

Try this:

DELETE FROM `relProductsPrices` 
WHERE `ID` IN ( 
     SELECT 
       tmp.ID 
     FROM (
       SELECT 
         scanA.ID 
       FROM 
         `relProductsPrices` as scanA 
       INNER JOIN `relProductsPrices` as scanB 
         ON scanA.ID        < scanB.ID 
         AND scanA.product   = scanB.product
         AND scanA.priceName = scanB.priceName
     ) as tmp
 );