Re: [sqlite] LIMIT keyword does work in an UPDATE statement

[Dennis Cote]

test mjom wrote:

   Hi, i'm beginning with SQLite and it seems that the keyword LIMIT is 
 not supported on an UPDATE statement.
 Does anybody would have a workaround to update only the very first 
 row matching the search criteria ? Ex : 
 
 create table tbl1 ( id integer primary key autoincrement, ref 
 integer, sts varchar(16));
 insert into tbl1 (ref,sts) values (10, 'ready' );
 insert into tbl1 (ref,sts) values (20, 'ready' ); insert into tbl1 
 (ref,sts) values (30, 'ready' );
 update tbl1 set sts='busy' where sts='ready' ORDER BY ref DESC LIMIT 1;
 
 => i would like to have only the third record (30,'busy') updated.
 
 Thank's in advance.



                
---------------------------------
Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs 
exceptionnels pour appeler la France et l'international.Téléchargez la version 
beta.
 

This should do the trick. Basically you use a select to find the id 
(i.e. the primary key) of the record to update, and then update that 
record only.

 update tbl1
   set sts='busy'
   where id in
     (select id from tbl1
     where sts='ready'
     order by ref desc
     limit 1);

HTH
Dennis Cote