16

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
Improve this question
7
  • 3
    Where is your ORDER BY ??? (your question says with ORDER BY) Commented Jan 31, 2012 at 13:38
  • Hi, I have tried with ORDER BY also, it gives the same error: Incorrect usage of UPDATE and ORDER BY. Commented Jan 31, 2012 at 13:43
  • Then show us that query - LIMIT is meaningless ORDER BY Commented Jan 31, 2012 at 13:47
  • UPDATE Ratemaster, Rates SET Ratemaster.Rate=Rates.Rate WHERE Ratemaster.user=Rates.user ORDER BY Ratemaster.id DESC LIMIT 1 Commented Jan 31, 2012 at 13:55
  • Show us some data. Which rows need updating? All of them? (I guess not). All rows of one user? All rows of one user and one product? Commented Jan 31, 2012 at 14:06

5 Answers 5

Reset to default
22

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
)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

In such a situation what else should be done to make it work, please help with a solution to my issue.
added an example query, but I don't know exactly what you want to do.
Hi, The above sql statement works, but the result is, all 3 records meeting user=user of Ratemaster has only 1 value from Rates table, What I need is 3 different values from Rates table, I think the limit is not working, hope the point is clear.
Hi Sascha, my requirement is simple, I need 3 values of Rates table(Rate) to be copied into Ratemaster field Rate where all 3 records are duplicates except for 1 field Rate column in it.
10

Salam You can use this method and work properly !

UPDATE Ratemaster, Rates 
SET Ratemaster.Rate=Rates.Rate 
WHERE Ratemaster.user=Rates.user 
ORDER BY Rates.id
LIMIT 1
Improve this answer

1 Comment

This won't work. ORDER BY and LIMIT can't be used in multi-table UPDATE.
3

Work It 100%

UPDATE table  SET Sing='p'  ORDER BY sr_no  LIMIT 10;
Improve this answer

Comments

1

Read article about How to use ORDER BY and LIMIT on multi-table updates in MySQL

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.

Improve this answer

1 Comment

Lol, Just read that article and for a moment I was in shock. The guy described working on a project which seems to be exactly the one I work on ;)
-6

The problem is that LIMIT is only to be used with SELECT statements, as it limits the number of rows returned by the query.

From: http://dev.mysql.com/doc/refman/5.5/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

For prepared statements, you can use placeholders. The following statements will return one row from the tbl table:

SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a;

The following statements will return the second to sixth row from the tbl table:

SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows;

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

If LIMIT occurs within a subquery and also is applied in the outer query, the outermost LIMIT takes precedence. For example, the following statement produces two rows, not one:

(SELECT ... LIMIT 1) LIMIT 2;

Improve this answer

10 Comments

Hi, All I need is to update from Table B field1 with Table A field2 where all records are duplicates for one user, need your help to solve this situation, with out limit, UPDATE command updates all the records with same value
@user1114409: The query you specified will simply do what you told it to (i.e. the content of SET) for ALL fields matching the WHERE clause. What is the layout of your table ? Maybe Ratemaster.user=Rates.user resolves to true more often than you think.
Hi Karolos, Yes you are right the where clause is what i need to limit, so that it does update for only record for matching criteria. In the above question I have clearly given the layout of the table, it is very simple. Check 2nd paragraph for the same.
@user1114409: Maybe it's simple to you but I don't seem to understand what it is you want after your explanations. You say "I need 3 values of Rates table(Rate) to be copied into Ratemaster field Rate where all 3 records are duplicates except for 1 field Rate column in it"; could you update your post with a few dummy rows as an example ? How do you expect that three duplicate records from ratemaster be filled with three different (?) rates from table rates ? How is the DB supposed to know which of the 3 values from table rates corresponds to those form ratemaster? Sorry I can't figure this
Limit can be used with "Update" as well: dev.mysql.com/doc/refman/5.0/en/update.html
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.