X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CC3C032A288 for ; Tue, 19 Oct 2004 00:17:42 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64912-08 for ; Mon, 18 Oct 2004 23:17:33 +0000 (GMT) Received: from DCIMAIL.dexterchaney.local (guarddog.dexterchaney.com [206.253.218.155]) by svr1.postgresql.org (Postfix) with SMTP id C020132A17D for ; Tue, 19 Oct 2004 00:17:34 +0100 (BST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message Subject: UPDATE Query Example MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C4B568.F6C3E180" Date: Mon, 18 Oct 2004 16:19:48 -0700 Message-ID: <5E8F9F5B63726C48836757FE673B584E010EE4C2@dcimail.dexterchaney.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: UPDATE Query Example Thread-Index: AcS1aPZrpU9v9q0rRaWzoHAkHL09hg== From: "Mark Dexter" To: Cc: "Richard Huxton" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200410/48 X-Sequence-Number: 2617 This is a multi-part message in MIME format. ------_=_NextPart_001_01C4B568.F6C3E180 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I think it could be helpful to users to have another example in the section VI. Reference / I. SQL Commands / UPDATE / Examples. It is not obvious how to do a more complex UPDATE query with a From clause, e.g., one using a LEFT OUTER JOIN , as follows: Use a LEFT OUTER JOIN with FROM clause syntax. Update employees commission rate to the higher of their current rate plus 2 percent or the special bonus rate from the bonus_plan table. In this example, some employees may not have valid bonus_plan rows in the bonus_plan table. Note that the table being updated (employees) is named twice and joined to itself in the WHERE clause.=20=20 UPDATE employees SET commission_rate =3D=20 CASE when bp.commission_rate > employees.commission_rate + .02 then bp.commission_rate else employees.commission_rate + .02 end FROM employees e=20 LEFT OUTER JOIN bonus_plan bp ON e.bonus_plan =3D bp.planid WHERE employees.employeeid =3D e.employeeid Thanks. Mark Dexter ------_=_NextPart_001_01C4B568.F6C3E180 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable UPDATE Query Example

I think it could be helpful to users to ha= ve another example in the section VI. Reference / I. SQL Commands / UPDATE = / Examples.  It is not obvious how to do a more complex UPDATE query w= ith a From clause, e.g., one using a LEFT OUTER JOIN , as follows:

Use a LEFT OUTER JOIN with FROM clause syn= tax.  Update employees commission rate to the higher of their current = rate plus 2 percent or the special bonus rate from the bonus_plan table.&nb= sp; In this example, some employees may not have valid bonus_plan rows in t= he bonus_plan table.  Note that the table being updated (employees) is= named twice and joined to itself in the WHERE clause. 

UPDATE employees SET commission_rate =3D <= /FONT>
CASE when bp.commission_rate > employe= es.commission_rate + .02 then bp.commission_rate else employees.commission_= rate + .02 end

FROM employees e
LEFT OUTER JOIN bonus_plan bp ON
e.bonus_plan =3D bp.planid
WHERE employees.employeeid =3D e.employee= id

Thanks.  Mark Dexter

= ------_=_NextPart_001_01C4B568.F6C3E180--