MySQL - UPDATE query with LIMIT

Question

I want to update rows in my table, starting from 1001 to 1000.

I tried the following query:

UPDATE `oltp_db`.`users` SET p_id = 3 LIMIT 1001, 1000 

• This gives me a syntax error. It's right? I am making any mistake here.
• Can we restrict the update in this way?

Also, the rows I'm trying to update are Null for a p_id column of INTEGER data type. Because of this, I cannot even update using the following query:

 UPDATE `oltp_db`.`users` SET p_id = 3 WHERE p_id = null 

• Is my previous request correct?
• What can be done to achieve this?

Answer 1

When working with zero = does not correspond to zero 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 only with row count

Answer 2

If you want to update multiple rows using a restriction 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 3

I would suggest a two-step request

I assume that you have an auto-incrementing primary key, because you say your PC is (max + 1), which sounds like the definition of an auto-detect key.
I call PK id , replace everything you call with.

1 - indicate the primary key number for column 1000.

 SELECT @id:= id FROM smartmeter_usage LIMIT 1 OFFSET 1000 

2 - refresh 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 check if I have made a mistake individually; you may need to add or subtract 1 somewhere.

Answer 4

 UPDATE `smartmeter_usage`.`users_reporting` SET panel_id = 3 LIMIT 1001, 1000 

This query is incorrect (or at least I don’t know how to use the restriction in UPDATE queries), you must put the where clause on the primary key (this assumes that you have the auto_increment column as your primary key, unless you provide a more detailed information):

 UPDATE `smartmeter_usage`.`users_reporting` SET panel_id = 3 WHERE primary_key BETWEEN 1001 AND 2000 

For the second request you should 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 should query (with reverse steps, as noted in the comment):

 UPDATE `smartmeter_usage`.`users_reporting` SET panel_id = 3 WHERE `MAX+1` BETWEEN 1001 AND 2000 

To update rows from MAX + 1 from 1001 to 2000 (including 1001 and 2000)

Answer 5

You should use IS, not = to compare with NULL.

 UPDATE `smartmeter_usage`.`users_reporting` SET panel_id = 3 WHERE panel_id IS null 

The LIMIT in MySQL when applied to an update does not allow you to specify an offset.

Answer 6

You can do this with LIMIT, but not with LIMIT and OFFSET.

Answer 7

You should consider using ORDER BY if you intend to restrict your UPDATE, because otherwise it will be updated when the table is ordered, which may be incorrect.

But, as A says, it only limits row_count, not offset.

Answer 8

For people, get this post by searching for "MySQL update limit", trying to avoid disabling safe update mode when update with multiple table syntax.

Since the official document of the state

For multi-table syntax, UPDATE updates the rows in each table named in table_references that satisfy the conditions. In this case, you cannot use ORDER BY and LIMIT.

stack overflow
I think this answer is very helpful. This gives an example.

UPDATE CLIENTS SET countryCode = 'USA' WHERE country = 'USA'; - which gives an error, you simply write:

CLIENT UPDATE SET countryCode = 'USA' WHERE (country = 'USA' AND customerNumber <> 0); - Since customerNumber is the primary key, you no longer have error 1175.

What I want, but will 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; 

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 does not attract too much attention (votes), I am posting more explanations. Hope this can help others.

Answer 9

In addition to the nested approach described above, you can perform LIMIT using JOIN for 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 more satisfied with this structure.