user1114409 user1114409 - 1 year ago 50
MySQL Question

UPDATE with ORDER BY and LIMIT not working in 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

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

Answer Source

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