Those with a SqlServer background will be familiar with the UPDATE .. FROM syntax. 

For example (totally made up)

update employee_bonus
   set bonus = 0
 from employee_bonus b
 inner join employees e on b.employee_id = e.employee_id
 where e.bonus_eligible = 'N'

Those who transitioned from SqlServer to Oracle might find the absence of the UPDATE FROM a significant loss.  The best Oracle alternative that I know of is as follows.

update ( select bonus
           from employee_bonus b
          inner join employees e on b.employee_id = e.employee_id
          where e.bonus_eligible = 'N' ) t
 set t.bonus = 0

Actually, if you look at it, they are very similar.

In fact Oracle has quite a bit of power in the UPDATE.  Check out this reference.  The syntax in the statement above utilizes the subquery1 block in the diagram below.

Happy coding.
posted on Wednesday, June 18, 2008 11:19 PM
Filed Under [ Oracle Database ]

Comments

Gravatar
# re: Oracle Update with Join
posted by Bostjan
on 8/18/2008 5:38 AM
Nice article!
It solved my problem.

thank you
Gravatar
# re: Oracle Update with Join
posted by Minal
on 9/30/2008 10:38 PM
Cheers, saved me some minutes...:-)
Your Comment




 
Please add 8 and 8 and type the answer here: