MySQL - UPDATE query with LIMIT

Question

I want to update rows in my table with starting from 1001 to next 1000   I tried with following query   UPDATE  oltp db   users  SET p id   3 LIMIT 1001  1000    This is giving me syntax error  Is this correct  am I doing any mistake here  Can we limit update in this way    Also  the rows that I am trying to update are having Null value for the column p id which is having data type INTEGER  Due to this I am not even able to update using following query   UPDATE  oltp db   users  SET p id   3 WHERE p id   null    Is my above query correct  What can be done to achieve this

User · Answer

You should use IS rather than   for comparing to NULL   UPDATE  smartmeter usage   users reporting  SET panel id   3 WHERE panel id IS null   The LIMIT clause in MySQL when applied to an update does not permit an offset to be specified

User · Answer

You can do it with a LIMIT  just not with a LIMIT and an OFFSET

User · Answer

If you want to update multiple rows using limit in MySQL you can use this construct   UPDATE table name SET name  test  WHERE id IN       SELECT id FROM           SELECT id FROM table name          ORDER BY id ASC           LIMIT 0  10       tmp

User · Answer

For people get this post by search  update limit MySQL   trying to avoid turning off the safe update mode when facing update with the multiple-table syntax   Since the offical document state     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    https   stackoverflow com a 28316067 1278112 I think this answer is quite helpful  It gives an example     UPDATE customers SET countryCode    USA        WHERE country    USA                 -- which gives the error  you just write       UPDATE customers SET countryCode    USA        WHERE  country    USA  AND customerNumber  lt   0   -- Because customerNumber is a primary key you got no error 1175 any more    What I want but would raise error code 1175   UPDATE table1 t1         INNER JOIN     table2 t2 ON t1 name   t2 name  SET      t1 column   t2 column WHERE     t1 name   t2 name    The working edition  UPDATE table1 t1         INNER JOIN     table2 t2 ON t1 name   t2 name  SET      t1 column   t2 column WHERE      t1 name   t2 name and t1 prime key   0     Which is really simple and elegant  Since the original answer doesn t get too much attention  votes   I post more explanation  Hope this can help others

User · Answer

You should highly consider using an ORDER BY if you intend to LIMIT your UPDATE  because otherwise it will update in the ordering of the table  which might not be correct   But as Will A said  it only allows limit on row count  not offset

User · Answer

When dealing with null    does not match the null values  You can use IS NULL or IS NOT NULL  UPDATE  smartmeter usage   users reporting   SET panel id   3 WHERE panel id IS NULL   LIMIT can be used with UPDATE but with the row count only

User · Answer

UPDATE  smartmeter usage   users reporting  SET panel id   3 LIMIT 1001  1000   This query is not correct  or at least i don t know a possible way to use limit in UPDATE queries    you should put a where condition on you primary key  this assumes you have an auto increment column as your primary key  if not provide more details    UPDATE  smartmeter usage   users reporting  SET panel id   3 WHERE primary key BETWEEN 1001 AND 2000   For the second query you must use IS  UPDATE  smartmeter usage   users reporting  SET panel id   3 WHERE panel id is null   EDIT - if your primary key is a column named MAX 1 you query should be  with backticks as stated correctly in the comment     UPDATE  smartmeter usage   users reporting  SET panel id   3 WHERE  MAX 1  BETWEEN 1001 AND 2000   To update the rows with MAX 1 from 1001 TO 2000  including 1001 and 2000

User · Answer

I would suggest a two step query  I m assuming you have an autoincrementing primary key because you say your PK is  max 1  which sounds like the definition of an autioincrementing key  I m calling the PK id  substitute with whatever your PK is called     1 - figure out the primary key number for column 1000   SELECT  id   id FROM smartmeter usage LIMIT 1 OFFSET 1000   2 - update the table   UPDATE smartmeter usage users reporting SET panel id   3  WHERE panel id IS NULL AND id  gt    id  ORDER BY id  LIMIT 1000   Please test to see if I didn t make an off-by-one error  you may need to add or subtract 1 somewhere

User · Answer

In addition to the nested approach above  you can accomplish the application of theLIMIT using JOIN on the same table   UPDATE  table name  INNER JOIN  SELECT  id  from  table name  order by  id  limit 0 100  as t2 using   id   SET  name     test    In my experience the mysql query optimizer is happier with this structure

[mysql] MySQL - UPDATE query with LIMIT

Examples related to mysql

Examples related to limit

Examples related to sql-update