mysql limit 不起作用_使用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.

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.

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:

Incorrect usage of UPDATE and LIMIT

UPDATE Ratemaster, Rates

SET Ratemaster.Rate=Rates.Rate

WHERE Ratemaster.user=Rates.user

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

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.

Try the following:

UPDATE Ratemaster

SET Ratemaster.Rate =

(

SELECT Rates.Rate

FROM Rates

WHERE Ratemaster.user = Rates.user

ORDER BY Rates.id

LIMIT 1

)