使用ORDER BY和LIMIT进行的更新在MYSQL中不起作用 [英] UPDATE with ORDER BY and LIMIT not working in MYSQL

问题描述

我是MYSQL的新手，无法解决甚至在该论坛上回答太多，也无法识别此语句中的错误.我正在使用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.

我有2个表:Ratemaster和Rates，客户可以在其中拥有1个具有不同费率的产品. 因此，有一个重复的客户和产品字段，只有费率字段更改. 现在，表Ratemaster具有所有字段:ID，客户代码，产品，费率，用户 而表费率"只有:ID，客户代码，费率，用户. -用户字段用于检查session_user.

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.

现在，表Ratemaster具有3条记录，所有字段的值都相同，但Rate字段为空. 桌费有不同的费率. 我想从Rates表中的Ratemaster中更新所有价格.我无法使用UPDATE和LIMIT mysql命令执行此操作，它给出错误消息:

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:

错误使用UPDATE和LIMIT

Incorrect usage of UPDATE and LIMIT

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

推荐答案

通常，您可以在UPDATE语句中使用LIMIT和ORDER，但对于您而言，则不能如

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会更新每个名为的表中的行 在满足条件的table_references中.在这种情况下，订购 不能使用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 Ratemaster
SET Ratemaster.Rate =
(
    SELECT Rates.Rate
    FROM Rates
    WHERE Ratemaster.user = Rates.user
    ORDER BY Rates.id
    LIMIT 1
)

这篇关于使用ORDER BY和LIMIT进行的更新在MYSQL中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！