mysql + 更新前 n 个

发布于 2024-07-16 18:20:38 字数 413 浏览 5 评论 0原文

我有一个这样的查询：

update  table
set     status = 1
where   status = 2;

但我只想对前 400 个执行此操作。我尝试添加“限制 0, 400”（就像我在查询中一样），但这不起作用。我做了一些搜索，mysql 似乎不像 sql server 那样支持 TOP(n) 命令。

知道我该怎么做吗？

编辑：为了将来的参考，我使用以下样式进行选择，效果很好：

select  * 
from    table
where   ... limit 0, 400;

但在更新中，无论出于何种原因，它都无法与“0，”一起使用。我会认为这种不一致且模棱两可的行为，但是哦，好吧。

原文

I've got a query like this:

update  table
set     status = 1
where   status = 2;

but I'd only like to do this to the top 400. I tried adding a 'limit 0, 400' (like I would in a query) but that didn't work. I did some searching and mysql doesn't seem to support the TOP(n) command as sql server does.

Any idea how I'd do this?

edit: for future reference, I was using the following style for selects, which worked fine:

select  * 
from    table
where   ... limit 0, 400;

but in the update it wouldn't work with the "0, " for whatever reason. I would consider this inconsistent and ambiguous behaviour, but oh well.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暖伴 2024-07-23 18:20:38

UPDATE  table
SET     status = 1
WHERE   status = 2
ORDER BY id
LIMIT 400

检查MySQL 5.2.0-falcon-alpha-community-nt-log，确认工作正常。

在您的情况下，LIMIT 0, 400 中的 0 不起作用。

您不能使用 UPDATE 的 LIMIT 中的下限。

UPDATE  table
SET     status = 1
WHERE   status = 2
ORDER BY id
LIMIT 400

Checked in MySQL 5.2.0-falcon-alpha-community-nt-log, confirmed working.

In your case it's 0 in LIMIT 0, 400 that does not work.

You cannot use the lower bound in UPDATE's LIMIT.

回复收藏 0 原文

无声无音无过去 2024-07-23 18:20:38

试试这个：

update  table
set     status = 1
where   status = 2
LIMIT 400

您还可以按子句放置 order by 子句

update  table
set     status = 1
where   status = 2
ORDER BY id
LIMIT 400

Try this:

update  table
set     status = 1
where   status = 2
LIMIT 400

You can also put an order by clause

update  table
set     status = 1
where   status = 2
ORDER BY id
LIMIT 400

回复收藏 0 原文

从来不烧饼 2024-07-23 18:20:38

以供将来参考，不要忘记将安全模式设置为关闭。

SET SQL_SAFE_UPDATES = 0;
update YOUR_DATABASE_NAME.TABLE_NAME
set COLUMN_NAM = 0
limit 400;
SET SQL_SAFE_UPDATES = 1;

例如，MySQL 不允许您更新记录，以防止您在安全模式下犯错误，因此您必须禁用它才能更新记录。

其他语法非常先进。
limit 会将返回的记录数量限制为您选择的数量。

for future reference don't forget to set the safe mode off

SET SQL_SAFE_UPDATES = 0;
update YOUR_DATABASE_NAME.TABLE_NAME
set COLUMN_NAM = 0
limit 400;
SET SQL_SAFE_UPDATES = 1;

MySQL for example does not allow you to update records to prevent you from making mistakes with something called safe mode, so you will have to disable it to be able to update your records.

The other syntax is pretty forward.
limit will restrict the number of records returned to a number of your choosing.

回复收藏 0 原文

~没有更多了~