Delete a second row if it's found in Mysql

Question

Im trying to delete a row in MySql if there are 2 rows for a merchant. It doesn't matter which row is deleted.

When I run:

SELECT * FROM products.merchant_configs WHERE `configKey` = 'CHECK_BALANCE_SUBJECT'
AND merchantId = 6;

I see:

Now I try to delete one of the rows:

DELETE FROM products.merchant_configs
WHERE id = (
    SELECT id FROM products.merchant_configs WHERE `configKey` = 'CHECK_BALANCE_SUBJECT'
    AND merchantId = 6
    LIMIT 2 OFFSET 1
);

I receive the error:

Error Code: 1093. You can't specify target table 'merchant_configs' for update in FROM clause

What's going on?

First of all, note that LIMIT without ORDER BY is fairly meaningless - which renders the logic of your query unclear. — Strawberry, May 28 '19 at 11:23

score 0 · Answer 1 · answered May 28 '19 at 11:19

Nest the query inside another select statement:

DELETE FROM products.merchant_configs
WHERE id = (SELECT t.id FROM (
    SELECT id FROM products.merchant_configs WHERE `configKey` = 'CHECK_BALANCE_SUBJECT'
    AND merchantId = 6
    LIMIT 2 OFFSET 1
) t);

See the demo.

Delete a second row if it's found in Mysql

1 Answers1