Thread: UPDATE Query Example

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