mysql + 更新前 n

2022-01-17 00:00:00 sql-update sql mysql

我有一个这样的查询:

update  table
set     status = 1
where   status = 2;     

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

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.

知道我该怎么做吗?

为了将来参考,我使用以下样式进行选择,效果很好:

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

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

但在更新中,无论出于何种原因,它都不能与0"一起使用.我会考虑这种不一致和模棱两可的行为,但是好吧.

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.

推荐答案

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

签入MySQL 5.2.0-falcon-alpha-community-nt-log,确认工作正常.

在您的情况下,它是 0 in LIMIT 0, 400 不起作用.

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

您不能在 UPDATELIMIT 中使用下限.

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

相关文章