MySQL - 使用 LIMIT 更新查询

MySQL - UPDATE query with LIMIT

提问by srahul07

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

我想从 1001 到下一个 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?

1. 这给了我语法错误。这样对吗？我在这里做错了吗。
2. 我们可以通过这种方式限制更新吗？

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:

此外，我尝试更新的行的数据类型为 INTEGER 的列 p_id 具有 Null 值。因此，我什至无法使用以下查询进行更新：

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?

1. 我上面的查询是否正确？
2. 可以做些什么来实现这一目标？

采纳答案by Shakti Singh

When dealing with null, =does not match the null values. You can use IS NULLor IS NOT NULL

处理空值时，=不匹配空值。您可以使用IS NULL或IS NOT NULL

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

LIMITcan be used with UPDATEbut with the row countonly

LIMIT可以与UPDATE但与row count唯一一起使用

回答by Roopchand

If you want to update multiple rows using limit in MySQL you can use this construct:

如果要在 MySQL 中使用 limit 更新多行，可以使用以下构造：

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
)

回答by Johan

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.

我假设你有一个自动递增的主键，因为你说你的 PK 是 (max+1) 这听起来像是一个自动递增键的定义。
我打电话给 PK id，用你的 PK 来代替。

1 - figure out the primary key number for column 1000.

1 - 找出第 1000 列的主键编号。

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

2 - update the table.

2 - 更新表。

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.

请测试看看我是否没有犯错；您可能需要在某处加或减 1。

回答by Jerry

In addition to the nested approach above, you can accomplish the application of theLIMITusing JOINon the same table:

除了上面的嵌套方式，你还可以在同一张表上完成LIMITusing的应用JOIN：

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.

根据我的经验，mysql 查询优化器对这种结构更满意。

回答by Nicola Peluchetti

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 wherecondition on you primary key (this assumes you have an auto_increment column as your primary key, if not provide more details):

这个查询不正确（或者至少我不知道在 UPDATE 查询中使用限制的可能方法），你应该where在你的主键上放置一个条件（假设你有一个 auto_increment 列作为你的主键，如果没有提供更多细节）：

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

For the second query you must use IS

对于第二个查询，您必须使用 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):

编辑 - 如果您的 primary_key 是一个名为 MAX+1 的列，您应该查询（在评论中正确说明反引号）：

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)

从 1001 到 2000（包括 1001 和 2000）用 MAX+1 更新行

回答by user945389

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

您可以使用 LIMIT 来实现，但不能使用 LIMIT 和 OFFSET。

回答by Will A

You should use IS rather than = for comparing to NULL.

您应该使用 IS 而不是 = 来与 NULL 进行比较。

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

The LIMITclause in MySQL when applied to an update does not permit an offset to be specified.

LIMITMySQL 中的子句应用于更新时不允许指定偏移量。

回答by jishi

You should highly consider using an ORDER BYif you intend to LIMIT your UPDATE, because otherwise it will update in the ordering of the table, which might not be correct.

ORDER BY如果您打算限制 UPDATE，则应高度考虑使用，否则它将按照表的顺序进行更新，这可能不正确。

But as Will A said, it only allows limit on row_count, not offset.

但正如 Will A 所说，它只允许限制 row_count，而不是偏移量。

回答by Shihe Zhang

For people get this post by search "update limit MySQL" trying to avoid turning off the safe update modewhen facing updatewith the multiple-table syntax.

对于人们通过搜索“更新限制 MySQL”获得这篇文章，试图避免safe update mode在面对update多表语法时关闭。

Since the offical documentstate

由于官方文件状态

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.

对于多表语法，UPDATE 更新 table_references 中命名的每个表中满足条件的行。在这种情况下，不能使用 ORDER BY 和 LIMIT。

https://stackoverflow.com/a/28316067/1278112
I think this answer is quite helpful. It gives an example

https://stackoverflow.com/a/28316067/1278112
我认为这个答案很有帮助。它给出了一个例子

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.

更新客户 SET countryCode = 'USA' WHERE country = 'USA'; -- 这给出了错误，你只需写：

更新客户 SET countryCode = 'USA' WHERE (country = 'USA' AND customerNumber <> 0); -- 因为 customerNumber 是一个主键，所以你不会再有错误 1175 了。

What I want but would raise error code 1175.

我想要但会引发错误代码 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.

这真的很简单和优雅。由于原始答案没有得到太多关注（投票），我发布了更多解释。希望这可以帮助其他人。