使用 ORDER BY 和 LIMIT 进行更新在 MYSQL 中不起作用

UPDATE with ORDER BY and LIMIT not working in MYSQL

提问by user1114409

I am new to MYSQL, and unable to resolve or even with so many answers on this forum, unable to identiy the error in this statement. I am using MYSQL database.

我是MYSQL的新手，在这个论坛上无法解决甚至有这么多答案，无法识别此语句中的错误。我正在使用 MYSQL 数据库。

I have 2 tables: Ratemaster and rates, in which a customer can have 1 product with different rates. Because of this, there is a duplication of customer and product fields, only the rate field changes. Now Table Ratemaster has all the fields : id, Customer code, Product, Rate, user whereas Table Rates has only: id, cust code, Rate, user. - user field is for checking session_user.

我有 2 个表：Ratemaster 和 Rates，其中客户可以拥有 1 个具有不同费率的产品。因此，客户和产品字段存在重复，只有费率字段发生变化。现在 Table Ratemaster 拥有所有字段：id、Customer code、Product、Rate、user 而 Table Rates 只有：id、cust code、Rate、user。- 用户字段用于检查 session_user。

Now Table Ratemaster has 3 records with all field values being same except Rate field empty. Table Rates has different rates. I want to have all rates to be updated in Ratemaster from Rates table. I am unable to do this with UPDATEand LIMITmysql command, it is giving error as:

现在表 Ratemaster 有 3 条记录，除 Rate 字段为空外，所有字段值都相同。表费率有不同的费率。我希望在 Ratemaster 中从 Rates 表更新所有费率。我不能同意这样做UPDATE和LIMITmysql命令，它是给错误如下：

Incorrect usage of UPDATE and LIMIT

UPDATE 和 LIMIT 的错误用法

UPDATE Ratemaster, Rates 
SET Ratemaster.Rate=Rates.Rate 
WHERE Ratemaster.user=Rates.user 
LIMIT 1

回答by Sascha Galley

Usually you can use LIMITand ORDERin your UPDATEstatements, but in your case not, as written in the MySQL Documentation 12.2.10. UPDATE Syntax:

通常你可以在你的语句中使用LIMIT和，但在你的情况下不能，如MySQL 文档 12.2.10 中所写。更新语法：ORDERUPDATE

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

对于多表语法，UPDATE 更新 table_references 中命名的每个表中满足条件的行。在这种情况下，不能使用 ORDER BY 和 LIMIT。

Try the following:

请尝试以下操作：

UPDATE Ratemaster
SET Ratemaster.Rate =
(
    SELECT Rates.Rate
    FROM Rates
    WHERE Ratemaster.user = Rates.user
    ORDER BY Rates.id
    LIMIT 1
)

回答by Alireza Balvardi

Salam You can use this method and work properly !

Salam 您可以使用此方法并正常工作！

UPDATE Ratemaster, Rates 
SET Ratemaster.Rate=Rates.Rate 
WHERE Ratemaster.user=Rates.user 
ORDER BY Rates.id
LIMIT 1

回答by Sandeep Sharma

Work It 100%

工作 100%

UPDATE table  SET Sing='p'  ORDER BY sr_no  LIMIT 10;  

回答by John Woo

Read article about How to use ORDER BY and LIMIT on multi-table updates in MySQL

阅读有关如何在 MySQL 中的多表更新中使用 ORDER BY 和 LIMIT 的文章

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

对于多表语法，UPDATE 更新 table_references 中命名的每个表中满足条件的行。在这种情况下，不能使用 ORDER BY 和 LIMIT。

回答by Karolos

The problem is that LIMIT is only to be used with SELECT statements, as it limits the number of rows returned by the query.

问题是 LIMIT 只能与 SELECT 语句一起使用，因为它限制了查询返回的行数。

From: http://dev.mysql.com/doc/refman/5.5/en/select.html

来自：http: //dev.mysql.com/doc/refman/5.5/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

For prepared statements, you can use placeholders. The following statements will return one row from the tbl table:

SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a;

The following statements will return the second to sixth row from the tbl table:

SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows;

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

If LIMIT occurs within a subquery and also is applied in the outer query, the outermost LIMIT takes precedence. For example, the following statement produces two rows, not one:

(SELECT ... LIMIT 1) LIMIT 2;

LIMIT 子句可用于限制 SELECT 语句返回的行数。LIMIT 接受一个或两个数字参数，它们都必须是非负整数常量，但以下情况除外：

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

有两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为 0（不是 1）：

SELECT * FROM tbl LIMIT 5,10; # 检索第 6-15 行

要检索从某个偏移量到结果集末尾的所有行，您可以为第二个参数使用一些大数字。此语句检索从第 96 行到最后一行的所有行：

SELECT * FROM tbl LIMIT 95,18446744073709551615;

使用一个参数，该值指定从结果集的开头返回的行数：

SELECT * FROM tbl LIMIT 5; # 检索前 5 行

换句话说，LIMIT row_count 等价于 LIMIT 0, row_count。

对于准备好的语句，您可以使用占位符。以下语句将从 tbl 表中返回一行：

设置@a=1; 从 'SELECT * FROM tbl LIMIT 准备 STMT ？'; 使用@a 执行STMT；

以下语句将从 tbl 表中返回第二到第六行：

设置@skip=1; 设置@numrows=5; 从 'SELECT * FROM tbl LIMIT ?, ?' 中准备 STMT；使用@skip、@numrows 执行 STMT；

为了与 PostgreSQL 兼容，MySQL 还支持 LIMIT row_count OFFSET 偏移语法。

如果 LIMIT 出现在子查询中并且也应用于外部查询，则最外面的 LIMIT 优先。例如，以下语句生成两行，而不是一行：

(选择 ... 限制 1) 限制 2;