UPDATE with ORDER BY and LIMIT not working in MYSQL

Question

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

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