Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3F2146337B8 for ; Tue, 11 Aug 2009 07:55:18 -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 74965-09 for ; Tue, 11 Aug 2009 10:55:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f183.google.com (mail-yw0-f183.google.com [209.85.211.183]) by mail.postgresql.org (Postfix) with ESMTP id 593F0633655 for ; Tue, 11 Aug 2009 07:55:07 -0300 (ADT) Received: by ywh13 with SMTP id 13so4567178ywh.15 for ; Tue, 11 Aug 2009 03:55:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=BgqpMs8j2D03Dqj/p+wc0QkVGY2ev6c8TU+MH4J5c00=; b=i9P9LbUnnIdLtscgQltNNfLh9uFHN4PbkChYq8kThhGFyAvhU9cvnw4CKlhiM98zFq L5aw2jeCLpWoFfIw0/6JEPIy8E6hTQ22ZJ25IPk2cpVX4L2UiiNFyZt01brAdC1lz97S q0DAnnxdIeWSi4ve9ehP2ujNVCXXOyR3XMVEM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=SIdH1VXHArds7LT3tiAITuv1mGoMS5UingD5wRKGOd/KfQfLIa/x2OG6NupJS+VnCF SZp17Bt5Yfuvbw8Eb0PmTyq1eoSny/Qo8jaP6Df3GSFjBnCnJ1sKcGfCvCBipAcT6b6+ 4a90d2mTR+m+aUCmAEiUFhGxYyC5FDohGscnA= MIME-Version: 1.0 Received: by 10.150.153.21 with SMTP id a21mr10345806ybe.109.1249988105845; Tue, 11 Aug 2009 03:55:05 -0700 (PDT) In-Reply-To: <20090811062503.86993bf6.darcy@druid.net> References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com> <20090811062503.86993bf6.darcy@druid.net> Date: Tue, 11 Aug 2009 12:55:05 +0200 Message-ID: <162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com> Subject: Re: Updating a specific number of rows in pl/pgsql From: Pavel Stehule To: "D'Arcy J.M. Cain" Cc: Peter Headland , pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.524 tagged_above=-10 required=5 tests=AWL=1.075, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/42 X-Sequence-Number: 32951 2009/8/11 D'Arcy J.M. Cain : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" wrote: >> I can get the rows I want to update like this: >> >> =C2=A0 SELECT * >> =C2=A0 =C2=A0FROM queue >> =C2=A0 =C2=A0WHERE id =3D p_queue_id >> =C2=A0 =C2=A0ORDER BY rank >> =C2=A0 =C2=A0LIMIT p_number_of_items; >> >> Of course, there may not be p_number_of_items available in the queue. >> >> I want to update all the rows in the cursor in the same way: >> >> =C2=A0 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: > > =C2=A0UPDATE queue SET assigned =3D TRUE > =C2=A0WHERE queue_id IN (SELECT queue_id > =C2=A0 =C2=A0FROM queue > =C2=A0 =C2=A0WHERE id =3D p_queue_id > =C2=A0 =C2=A0ORDER BY rank > =C2=A0 =C2=A0LIMIT p_number_of_items); > there are one fast trick http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_re= moving p.s. replace DELETE by UPDATE regards Pavel Stehule > -- > D'Arcy J.M. Cain =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0De= mocracy is three wolves > http://www.druid.net/darcy/ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| =C2=A0and a sheep voting on > +1 416 425 1212 =C2=A0 =C2=A0 (DoD#0082) =C2=A0 =C2=A0(eNTP) =C2=A0 | =C2= =A0what's for dinner. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >