使用ORDER BY和LIMIT更新不在MYSQL中工作

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和rate，其中客户可以拥有1种不同价格的产品。因此，客户和产品字段重复，只有费率字段发生变化。现在Table Ratemaster具有所有字段：id，客户代码，产品，费率，用户，而Table Rates仅具有：id，cust代码，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 UPDATE and LIMIT mysql command, it is giving error as:

现在Table Ratemaster有3条记录，除了Rate字段为空外，所有字段值都相同。表费率有不同的费率。我想要从费率表中的Ratemaster更新所有费率。我无法使用UPDATE和LIMIT mysql命令执行此操作，它给出错误：

Incorrect usage of UPDATE and LIMIT

UPDATE和LIMIT的使用不正确

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

4 个解决方案

#1

Usually you can use LIMIT and ORDER in your UPDATE statements, but in your case not, as written in the MySQL Documentation 12.2.10. UPDATE Syntax:

通常，您可以在UPDATE语句中使用LIMIT和ORDER，但在您的情况下不是，如MySQL文档12.2.10中所述。更新语法：

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
)

#2

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

#3

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。

#4

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:

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.

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

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

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

SELECT * FROM tbl LIMIT 5,10; ＃检索行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:

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

SELECT * FROM tbl LIMIT 95,18446744073709551615;

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

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

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

换句话说，LIMIT row_count相当于LIMIT 0，row_count。

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

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

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

SET @ a = 1;从'SELECT * FROM tbl LIMIT'中预备STMT？使用@a执行STMT;

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

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

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

SET @ skip = 1; SET @ numrows = 5;准备STMT从'SELECT * FROM tbl LIMIT？，？';使用@skip执行STMT，@ numrows;

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

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

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:

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

(SELECT ... LIMIT 1) LIMIT 2;

（SELECT ... LIMIT 1）LIMIT 2;