From: tp <tp@emaze.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE & LIMIT together?
Date: Tue, 3 Sep 2002 08:37:27 +0000
Message-ID: <20020903083727.GH5674@emaze.net> (raw)
In-Reply-To: <200208281701.g7SH1aI20777@candle.pha.pa.us>
References: <20020828160929.GL20155@emaze.net>
<200208281701.g7SH1aI20777@candle.pha.pa.us>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tp@emaze.net, pgman@candle.pha.pa.us
Subject: Re: UPDATE & LIMIT together?
In-Reply-To: <20020903083727.GH5674@emaze.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox