mysql + update top n

Question

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.

Answer 1

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

Answer 2

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.