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
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
This question is related to
mysql
limit
sql-update
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.
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.
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
)
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.
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.
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.
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 can do it with a LIMIT, just not with a LIMIT and an OFFSET.
Source: Stackoverflow.com