mysql - 如果在 Mysql 中找到第二行，则删除它

Question

这个问题已经有答案了:

MySQL Error 1093 - Can't specify target table for update in FROM clause (16 个回答)

已关闭 4 年前。

如果商家有 2 行，我会尝试删除 MySql 中的一行。删除哪一行并不重要。

当我运行时:

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

我明白了:

现在我尝试删除其中一行:

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

我收到错误:

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

发生什么事了？

Answer 1

将查询嵌套在另一个选择语句中:

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

请参阅demo .