Mysql + update top n

Question

I have a query like this:

update table set status = 1 where status = 2; 

but I would like to do this with only 400. I tried to add "limit 0, 400" (as in the request), but that did not work. I did some searches, and mysql does not seem to support the TOP (n) command, as sql server does.

Any idea how I would do this?

edit: for future reference, I used the following style to select which worked fine:

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

but in the update for some reason it will not work with "0". I would consider this inconsistent and ambiguous behavior, but good.

Answer 1

 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 operation.

In your case, it is 0 in LIMIT 0, 400 , which does not work.

You cannot use the lower bound in UPDATE LIMIT .

Answer 2

Try the following:

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

You can also put offer on offer

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