带有限制的 MySQL-UPDATE 查询

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

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.

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)

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.

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.

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
)

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

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.

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.