MySQL 错误：UPDATE 和 LIMIT 的错误使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4291833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error: Incorrect usage of UPDATE and LIMIT
提问by HELP
How can I correct this problem so that my MySQL code works correctly.
如何更正此问题，以便我的 MySQL 代码正常工作。
Here is my MySQL code that gives me the problem.
这是我的 MySQL 代码，它给了我这个问题。
$q = "UPDATE users INNER JOIN contact_info ON contact_info.user_id = users.user_id SET active.users = NULL WHERE (email.contact_info = '" . mysqli_real_escape_string($mysqli, $x) . "' AND active.users = '" . mysqli_real_escape_string($mysqli, $y) . "') LIMIT 1";
$r = mysqli_query ($mysqli, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($mysqli));
回答by Marc B
回答by Roopchand
**if you want to update multiple rows using limit in mysql...directly limit you cant use try like this**
**如果你想在mysql中使用限制更新多行...直接限制你不能像这样使用* *
UPDATE table_name SET name='test'
WHERE id IN (
SELECT id FROM (
SELECT id FROM table_name
ORDER BY id ASC
LIMIT 0, 10
) tmp
);
回答by Kamran Mushtaq
For the multiple-table syntax, UPDATEupdates rows in each table named in
table_references that satisfy the conditions. In this case, ORDER BYand LIMITcannot be used
对于多表语法，UPDATE更新 table_references 中命名的每个表中满足条件的行。在这种情况下，ORDER BY并LIMIT不能使用
回答by Shihe Zhang
@Marc B provides the reason, why updatenormally can't work with limit.
@Marc B 提供了原因，为什么update通常不能使用limit.
And @Roopchand also provide a solution.
而且@Roopchand 也提供了解决方案。
For people like me, who is trying to avoid turning off the safe update mode
对于像我这样试图避免关闭 safe update mode
https://stackoverflow.com/a/28316067/1278112
This answer is quite helpful. It give an example
https://stackoverflow.com/a/28316067/1278112
这个答案很有帮助。它举了一个例子
UPDATE customers SET countryCode = 'USA' WHERE country = 'USA'; -- which gives the error, you just write:
UPDATE customers SET countryCode = 'USA' WHERE (country = 'USA' AND customerNumber <> 0); -- Because customerNumber is a primary key you got no error 1175 any more.
更新客户 SET countryCode = 'USA' WHERE country = 'USA'; -- 这给出了错误，你只需写：
更新客户 SET countryCode = 'USA' WHERE (country = 'USA' AND customerNumber <> 0); -- 因为 customerNumber 是一个主键，所以你不会再有错误 1175 了。
And when I face updatewith the multiple-table syntax, it also worked.
当我面对update多表语法时，它也有效。
What I want but would raise error code 1175.
我想要但会引发错误代码 1175。
UPDATE table1 t1
INNER JOIN
table2 t2 ON t1.name = t2.name
SET
t1.column = t2.column
WHERE
t1.name = t2.name;
The working edition
工作版
UPDATE table1 t1
INNER JOIN
table2 t2 ON t1.name = t2.name
SET
t1.column = t2.column
WHERE
(t1.name = t2.name and t1.prime_key !=0);
Which is really simple and elegant. Since the original answer doesn't get too much attention (votes), I post more explanation. Hope this can help others.
这真的很简单和优雅。由于原始答案没有得到太多关注（投票），我发布了更多解释。希望这可以帮助其他人。
回答by Shaolin
I know it is an old question but it is the first link when googling this error. There is a workaround to solve this problem without performance issue (depending of your indexes) by using a subquery.
我知道这是一个老问题，但它是谷歌搜索此错误时的第一个链接。有一种解决方法可以通过使用子查询来解决此问题，而不会出现性能问题（取决于您的索引）。
UPDATE table1 t1
JOIN (SELECT t1.id
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
AND t2.some_criteria = 'some_value'
WHERE t1.other_criteria = 'other_value'
LIMIT 10000
) tmp ON tmp.id = t1.id
SET t1.field_to_update = 'new_value'
Because the LIMIT is inside the subquery, the join will match only the number rows of the clause LIMIT and thus the query will update only these rows.
因为 LIMIT 在子查询中，连接将只匹配子句 LIMIT 的行数，因此查询将只更新这些行。
