Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 5123E633F27
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 14:03:29 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 53501-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 17:03:16 +0000 (UTC)
X-Greylist: delayed 00:07:12.061534 by SQLgrey-1.7.6
Received: from mx3.actuate.com (unknown [212.140.245.163])
	by mail.postgresql.org (Postfix) with ESMTP id C0832634020
	for <pgsql-sql@postgresql.org>; Tue, 11 Aug 2009 14:02:30 -0300 (ADT)
Received: from exchange9.actuate.com ([192.168.213.9]) by mx3.actuate.com with
	Microsoft SMTPSVC(5.0.2195.6713); Tue, 11 Aug 2009 17:55:31 +0100
Received: from exchange8.actuate.com ([192.168.11.22]) by
	exchange9.actuate.com with Microsoft SMTPSVC(6.0.3790.3959);
	Tue, 11 Aug 2009 17:55:16 +0100
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Updating a specific number of rows in pl/pgsql
Date: Tue, 11 Aug 2009 09:55:36 -0700
Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C7292D8@exchange8.actuate.com>
In-Reply-To: <20090811062503.86993bf6.darcy@druid.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] Updating a specific number of rows in pl/pgsql
Thread-Index: AcoabiUHL3IrTRnTS2iF7BjJ6NZBAAANgh7g
References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com>
	<20090811062503.86993bf6.darcy@druid.net>
From: "Peter Headland" <pheadland@actuate.com>
To: "D'Arcy J.M. Cain" <darcy@druid.net>
Cc: <pgsql-sql@postgresql.org>
X-OriginalArrivalTime: 11 Aug 2009 16:55:16.0830 (UTC)
	FILETIME=[809E57E0:01CA1AA4]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.01 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, RDNS_NONE=0.1
X-Spam-Level: 
X-Archive-Number: 200908/45
X-Sequence-Number: 32954

>Assuming that there is a unique identifier on queue

Alas, there is not. The PK is made up of 4 columns.

--=20
Peter Headland
Architect
Actuate Corporation

-----Original Message-----
From: D'Arcy J.M. Cain [mailto:darcy@druid.net]=20
Sent: Tuesday, August 11, 2009 03:25
To: Peter Headland
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql

On Mon, 10 Aug 2009 17:52:36 -0700
"Peter Headland" <pheadland@actuate.com> wrote:
> I can get the rows I want to update like this:
>=20
>   SELECT *
>    FROM queue
>    WHERE id =3D p_queue_id
>    ORDER BY rank
>    LIMIT p_number_of_items;
>=20
> Of course, there may not be p_number_of_items available in the queue.
>=20
> I want to update all the rows in the cursor in the same way:
>=20
>   UPDATE queue SET assigned =3D TRUE;

Assuming that there is a unique identifier on queue, let's call it
queue_id, you should be able to do something like this:

  UPDATE queue SET assigned =3D TRUE
  WHERE queue_id IN (SELECT queue_id
    FROM queue
    WHERE id =3D p_queue_id
    ORDER BY rank
    LIMIT p_number_of_items);

--=20
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.