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
Incorrect usage of UPDATE and LIMIT
UPDATE Ratemaster, Rates
Usually you can use
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 )