public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mark Dexter <[email protected]>
To: [email protected]
Cc: Richard Huxton <[email protected]>
Subject: UPDATE Query Example
Date: Mon, 18 Oct 2004 16:19:48 -0700
Message-ID: <5E8F9F5B63726C48836757FE673B584E010EE4C2@dcimail.dexterchaney.local> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: UPDATE Query Example
  In-Reply-To: <5E8F9F5B63726C48836757FE673B584E010EE4C2@dcimail.dexterchaney.local>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox