Thread: UPDATE & LIMIT together?
Hi I want to SELECT at max. 10 rows and SET a variable for the select 10 rows with the same query. Under mysql i can use: UPDATE table SET uniq_iq=12345 LIMIT 10 SELECT * FROM table WHERE uniq_id=1234; This is not supported by postgres. Is there some easy solution that does not require locking? -tp
You have to use a subquery returning the tables primary key to the UPDATE: UPDATE tab SET x=1WHERE (primkey, col) IN ( SELECT primkey,col FROM tab ORDER BY col LIMIT 10) --------------------------------------------------------------------------- tp wrote: -- Start of PGP signed section. > Hi > > I want to SELECT at max. 10 rows and SET a variable for the > select 10 rows with the same query. > > Under mysql i can use: > UPDATE table SET uniq_iq=12345 LIMIT 10 > SELECT * FROM table WHERE uniq_id=1234; > > This is not supported by postgres. > > Is there some easy solution that does not require locking? > > > -tp -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Hi Folks, I am having troubles with a case statement in that I want to have the query select only those records that match a particular case. Here's my query: SELECT agency_contact_info.id,organization,department,city,state,description_of_ser vices, CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END + CASE WHEN agency_contact_info.languages_other_text ~ 'Mien' THEN 1 ELSE 0 END AS relevance FROM agency_contact_info WHERE (agency_contact_info.guideregion=1 AND list_online IS TRUE AND (agency_contact_info.id > 0 OR agency_contact_info.languages_other_text ~ 'Mien' )) ORDER BY relevance DESC, agency_contact_info.organization How do I add in the fact that I only want records where the CASE (as relevance) > 0? I've tried using it in the WHERE statement adding a HAVING statement and it doesn't like either. You will see a fair amount of redundancy in the statement above such as "CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END" and "agency_contact_info.id > 0" - this is because it is being built dynamically, and it makes it easier to build the addition blocks of the statement. Thanks in advance, Tom _______________________________ Tom Haddon IT Director The Better Health Foundation 414 Thirteenth Street, Suite 450 Oakland, CA 94612 (510) 444-5096 www.betterhealthfoundation.org _______________________________
On Wed, 28 Aug 2002, Tom Haddon wrote: > Hi Folks, > > I am having troubles with a case statement in that I want to have the query > select only those records that match a particular case. Here's my query: > > SELECT > agency_contact_info.id,organization,department,city,state,description_of_ser > vices, CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END + CASE WHEN > agency_contact_info.languages_other_text ~ 'Mien' THEN 1 ELSE 0 END AS > relevance > FROM agency_contact_info WHERE (agency_contact_info.guideregion=1 AND > list_online IS TRUE AND (agency_contact_info.id > 0 OR > agency_contact_info.languages_other_text ~ 'Mien' )) > ORDER BY relevance DESC, agency_contact_info.organization > > How do I add in the fact that I only want records where the CASE (as > relevance) > 0? I've tried using it in the WHERE statement adding a HAVING > statement and it doesn't like either. You will see a fair amount of I think you'll either need to duplicate the case statement or hide it in a subselect with the relevance check on the outer query (like select * from (select ... ) as a where relevance>0.
Hmm,, thanks so far, it helped. The query is horrible slow on full tables (>100.000 rows). Is there any other solution? I just want to have the 'next 10 entries' WHERE state=10 and update state=20. (so that on the next request i or another process only gets the new entires in queue). My query now looks like: UPDATE queue SET state=20 WHERE (id) IN (SELECT id FROM queue LIMIT 10) -tp Bruce Momjian(pgman@candle.pha.pa.us)@Wed, Aug 28, 2002 at 01:01:36PM -0400: > > You have to use a subquery returning the tables primary key to the > UPDATE: > > UPDATE tab SET x=1 > WHERE (primkey, col) IN ( > SELECT primkey,col FROM tab > ORDER BY col > LIMIT 10) > > --------------------------------------------------------------------------- > > tp wrote: > -- Start of PGP signed section. > > Hi > > > > I want to SELECT at max. 10 rows and SET a variable for the > > select 10 rows with the same query. > > > > Under mysql i can use: > > UPDATE table SET uniq_iq=12345 LIMIT 10 > > SELECT * FROM table WHERE uniq_id=1234;
tp wrote: > Hmm,, > > thanks so far, it helped. > > The query is horrible slow on full tables (>100.000 rows). > Is there any other solution? I just want to have the 'next 10 entries' > WHERE state=10 and update state=20. > (so that on the next request i or another process only gets the > new entires in queue). > > > My query now looks like: > UPDATE queue SET state=20 WHERE (id) IN (SELECT id FROM queue LIMIT 10) Without an ORDER BY, I am not sure what that LIMIT is returning. I don't know of a faster way. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073