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

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 3

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.

Answer 4

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 5

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

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)

Answer 7

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 8

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

Answer 9

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.

Answer 10

The limiting column name is not given but assuming it to be XX, then something like this should do. It is tested on MariaDB 10:

UPDATE `oltp_db`.`users` SET `p_id` = '3' WHERE `p_id` IS NULL AND `XX` > 1000 LIMIT 1000;

This will target the right records. The only unknown side-effect is that although the affected rows show the limit number, the actual changes may be less.