使用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屋!