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')); 

Answer

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.