EXPERT RESPONSE
There are three things to establish: which column, which row, and which value.
Which column is specified in the SET clause. To designate which column(s) to update,
you must name them. If the column is updated conditionally, you must still name it.
Which row is specified in the WHERE clause. This can be based on columns other than
those being updated.
Which value is specified, if necessary, using a CASE expression.
update salaries
set bonus =
case when job = 'dir' then 1000
when job = 'mgr' then 500
else 0 end
, salary = salary *
case when salary < 45000 then 1.02
else 1.04 end
where location = 'hq'
Bonus for directors and managers, plus a 2% increase for everyone under 45,000
and 4% for everyone over. Applies only to employees at home office.
|
