Skip site navigation (1) Skip section navigation (2)

UPDATE Query Example

From: "Mark Dexter" <MDEXTER(at)dexterchaney(dot)com>
To: <pgsql-docs(at)postgresql(dot)org>
Cc: "Richard Huxton" <dev(at)archonet(dot)com>
Subject: UPDATE Query Example
Date: 2004-10-18 23:19:48
Message-ID: 5E8F9F5B63726C48836757FE673B584E010EE4C2@dcimail.dexterchaney.local (view raw or flat)
Thread:
Lists: pgsql-docs
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.  

UPDATE employees SET commission_rate = 
CASE when bp.commission_rate > employees.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 = bp.planid
WHERE employees.employeeid = e.employeeid

Thanks.  Mark Dexter

pgsql-docs by date

Next:From: Hicham G. ElmonguiDate: 2004-10-19 14:21:26
Subject: code in docs gives me an error
Previous:From: Peter EisentrautDate: 2004-10-18 14:12:36
Subject: Re: SQL 2003 conformance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group