Search
Close this search box.

Oracle Update with Join

[Update Oct ‘09 In response to a number of comments on this post regarding problems with this approach, I’ve added a new blog entry with more details.  Check it out.]

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

For example (totally made up)

1: update employee_bonus
2: set bonus = 0 
3: from employee_bonus b 
4: inner join employees e on b.employee_id = e.employee_id
5: 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.

1: update (select bonus 
2: from employee_bonus b 
3: inner join employees e on b.employee_id = e.employee_id 
4: where e.bonus_eligible = 'N') t
5: 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.

Tags: OracleSQL

This article is part of the GWB Archives. Original Author: WILL SMITH

Related Posts