MySQL更新表时LIMIT与IN子查询联用报错问题求助

Got it, this is a super common gotcha with older MySQL versions—they don’t let you use LIMIT directly inside an IN subquery. Let’s walk through a few solid workarounds that play nicely with MySQL’s rules, plus fix a tiny syntax issue you might have missed.

方法1：嵌套子查询（兼容大多数MySQL版本） #

Wrap your LIMIT-containing subquery in a derived table. This tricks MySQL into first executing the limited result set, then using it in the outer IN clause—avoiding the unsupported syntax. Also, I renamed your order table to orders because order is a MySQL reserved word, which would cause another error otherwise!

UPDATE vouchers 
SET status = 6 
WHERE voucher_id IN (
    SELECT voucher_code 
    FROM (
        SELECT a.voucher_code 
        FROM products a 
        JOIN orders b ON a.order_id = b.id 
        WHERE a.voucher_code != '' 
        LIMIT 10
    ) AS temp_derived_table
);

方法2：使用JOIN进行更新（更高效） #

Using a JOIN for updates is often faster than subqueries, and it naturally avoids the LIMIT + IN restriction. Add DISTINCT if you want to prevent duplicate updates for the same voucher code:

UPDATE vouchers v
JOIN (
    SELECT DISTINCT a.voucher_code 
    FROM products a 
    JOIN orders b ON a.order_id = b.id 
    WHERE a.voucher_code != '' 
    LIMIT 10
) AS temp ON v.voucher_id = temp.voucher_code
SET v.status = 6;

方法3：窗口函数（MySQL 8.0+ only） #

If you’re running MySQL 8.0 or newer, ROW_NUMBER() gives you more control over row selection and reads cleaner:

UPDATE vouchers 
SET status = 6 
WHERE voucher_id IN (
    SELECT voucher_code 
    FROM (
        SELECT 
            a.voucher_code,
            ROW_NUMBER() OVER () AS row_num
        FROM products a 
        JOIN orders b ON a.order_id = b.id 
        WHERE a.voucher_code != ''
    ) AS temp
    WHERE row_num <= 10
);

Why did your original query fail? #

Older MySQL versions (5.7 and below) don’t support LIMIT inside IN/ALL/ANY subqueries because they can’t guarantee consistent semantic behavior for the limited result set. Wrapping the limited query in a derived table fixes this by forcing MySQL to resolve the limited set first, before using it in the outer condition.

内容的提问来源于stack exchange，提问作者Ankit