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

1. This is giving me syntax error. Is this correct? am I doing any mistake here.
2. 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

1. Is my above query correct?
2. What can be done to achieve this?

Answer 1

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
)

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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 >= @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.

Answer 6

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)