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

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.

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.

Answer 3

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