[SQL] UPDATE & LIMIT together?

[tp]

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



msg07235/pgp0.pgp
Description: PGP signature

Re: [SQL] UPDATE & LIMIT together?

[tp]

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([EMAIL PROTECTED])@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;

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]