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
