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

[Derrell . Lipman]

test mjom <[EMAIL PROTECTED]> writes:

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

How about something like

  UPDATE tbl1
    SET sts = 'busy'
    WHERE ref =
      (SELECT ref
         FROM tbl1
         WHERE sts = 'ready'
         ORDER BY ref DESC
         LIMIT 1);

Derrell