CODEWITHSUNDEEP
mysqlsqldatabase
Capfer
Capfer

Reputation: 901

Can't delete records from the same table using NOT EXISTS MySQL syntax

I want to perform deletion in the same table. I've tried different ways, but I still can't get it right. So, I got this error You can't specify target table 'tb' for update in FROM clause. I tried aliasing the tables, but nothing done. I can't figure out what is missing. I appreciate any ideas.

 DELETE FROM tb WHERE NOT EXISTS (SELECT * FROM tb t WHERE t.`merchantId` = 'A32WNPGI8GE4WW' AND t.`marketplaceId` IN ('A1AM78C64UM0Y8', 'A2EUQ1WTGCTBG2', 'ATVPDKIKX0DER'));

Upvotes: 1

Views: 1467

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

MySQL supports JOINs in DELETE and UPDATE. This works around the "same table" limitation in this database. For your logic, it could look like:

DELETE tb
    FROM tb LEFT JOIN
         tb tb2
         ON tb2.`merchantId` = 'A32WNPGI8GE4WW' AND
            tb2.`marketplaceId` IN ('A1AM78C64UM0Y8', 'A2EUQ1WTGCTBG2', 'ATVPDKIKX0DER')
    WHERE tb2.merchantID IS NULL;

This query is highly irregular, though. It is either deleting all rows or no rows.

If you just want to keep those rows, then this is the way to go:

DELETE tb FROM tb
    WHERE NOT (tb.`merchantId` = 'A32WNPGI8GE4WW' AND
               tb.`marketplaceId` IN ('A1AM78C64UM0Y8', 'A2EUQ1WTGCTBG2', 'ATVPDKIKX0DER')
              );

Or:

DELETE tb FROM tb
    WHERE tb.merchantId <> 'A32WNPGI8GE4WW' OR
          tb.marketplaceId NOT IN ('A1AM78C64UM0Y8', 'A2EUQ1WTGCTBG2', 'ATVPDKIKX0DER');

If there are NULL values in either column, then the logic also needs to take this into account.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

for avoid the update error when operating on same table you could try using a nested subquery for build a temp table 

 DELETE  FROM tb 
 WHERE NOT EXISTS ( 
    SELECT * 
    FROM (
      SELECT * 
      from tb
      WHERE `merchantId` = 'A32WNPGI8GE4WW' 
      AND `marketplaceId`  IN ('A1AM78C64UM0Y8', 'A2EUQ1WTGCTBG2', 'ATVPDKIKX0DER')
    ) t
  );

Upvotes: 2

Related Questions