LIMIT keyword does work in an UPDATE statement

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

LIMIT keyword does work in an UPDATE statement

test mjom
    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.
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT keyword does work in an UPDATE statement

Derrell Lipman
test mjom <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT keyword does work in an UPDATE statement

Dennis Cote
In reply to this post by test mjom
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
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT keyword does work in an UPDATE statement

Jim C. Nasby
In reply to this post by Derrell Lipman
On Tue, Jan 03, 2006 at 10:15:17AM -0500, [hidden email] wrote:

> test mjom <[hidden email]> 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);

That won't work. Instead:                                                                      
                                                                                               
UPDATE ...                                                                                      
    WHERE id =                                                                                  
        (SELECT id                                                                              
            FROM tbl1                                                                          
            WHERE ...                                                                          
        );
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT keyword does work in an UPDATE statement

Dennis Cote
In reply to this post by test mjom
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.
>  
>
>
I should clarify that my SQL is slightly different than Darrel's. By
using the primary key, which is guaranteed to be unique, I am certain
that only one record will be updated. Darrel's statement will update all
the records that have the same value in the ref column. This is not
necessarily the same thing.

Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: LIMIT keyword does work in an UPDATE statement

Derrell Lipman
In reply to this post by Derrell Lipman
"Jim C. Nasby" <[hidden email]> writes:

> On Tue, Jan 03, 2006 at 10:15:17AM -0500, [hidden email] wrote:
>> test mjom <[hidden email]> 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);
>
> That won't work. Instead:
>
> UPDATE ...
>     WHERE id =
>         (SELECT id
>             FROM tbl1
>             WHERE ...
>         );

Yeah, what he said. :-)

Duh!  Sorry about that.

Derrell
Reply | Threaded
Open this post in threaded view
|

RE: LIMIT keyword does work in an UPDATE statement

test mjom
In reply to this post by test mjom
Dennis, it works perfectly well, so thank you for your quick and relevant solution.

test mjom <[hidden email]> a écrit :  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.


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