RE: [sqlite] LIMIT keyword does work in an UPDATE statement
Dennis, it works perfectly well, so thank you for your quick and relevant solution. test mjom <[EMAIL PROTECTED]> 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.
Re: [sqlite] LIMIT keyword does work in an UPDATE statement
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Tue, Jan 03, 2006 at 10:15:17AM -0500, [EMAIL PROTECTED] wrote: >> 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); > > That won't work. Instead: > > UPDATE ... > WHERE id = > (SELECT id > FROM tbl1 > WHERE ... > ); Yeah, what he said. :-) Duh! Sorry about that. Derrell
Re: [sqlite] LIMIT keyword does work in an UPDATE statement
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
Re: [sqlite] LIMIT keyword does work in an UPDATE statement
On Tue, Jan 03, 2006 at 10:15:17AM -0500, [EMAIL PROTECTED] wrote: > 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); That won't work. Instead: UPDATE ... WHERE id = (SELECT id FROM tbl1 WHERE ... ); -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] LIMIT keyword does work in an UPDATE statement
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
Re: [sqlite] LIMIT keyword does work in an UPDATE statement
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
