Обсуждение: 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