Geeks With Blogs
Will Smith The Blog in Black

[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: ,
Posted on Wednesday, June 18, 2008 11:19 PM Oracle , Database | Back to top


Comments on this post: Oracle Update with Join

# re: Oracle Update with Join
Requesting Gravatar...
Nice article!
It solved my problem.

thank you
Left by Bostjan on Aug 18, 2008 5:38 AM

# re: Oracle Update with Join
Requesting Gravatar...
Cheers, saved me some minutes...:-)
Left by Minal on Sep 30, 2008 10:38 PM

# re: Oracle Update with Join
Requesting Gravatar...
Thanks a bunch, this saved me a ton of time and the nasty prospect of having to write some cursor-based nonsense.
Left by Tim on Jan 16, 2009 5:39 PM

# re: Oracle Update with Join
Requesting Gravatar...
it solved my problem to some extent.but i want to know can we update multiple values at the same time using a single querry. i tried but it shows "missing right parenthesis"
please send a reply to my id...
thankx...
Left by renu on Feb 17, 2009 4:30 AM

# re: Oracle Update with Join
Requesting Gravatar...
renu... you can update multiple values. There must be a syntax error in your statement. Take a look at this example:

update ( select profit_bonus,
merit_bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N' ) t
set t.profit_bonus = 0,
t.merit_bonus = 0;
Left by Will on Feb 17, 2009 11:47 AM

# re: Oracle Update with Join
Requesting Gravatar...
What if you want to update to a non constant value?

eg. the bonus should be 10% of the salary which is stored in the employees table
Left by David on Feb 25, 2009 9:40 AM

# re: Oracle Update with Join
Requesting Gravatar...
Hi David,

update ( select b.bonus, e.salary
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.1 * t.salary

Hope this helps!
Left by Will on Feb 26, 2009 8:23 AM

# re: Oracle Update with Join
Requesting Gravatar...
Hi David,

This time where the employee is eligible for a bonus :-)

update ( select b.bonus, e.salary
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'Y' ) t
set t.bonus = 0.1 * t.salary

Hope this helps!
Left by Will on Feb 26, 2009 8:26 AM

# re: Oracle Update with Join
Requesting Gravatar...
Hello, when I try this, it gives me the following error:
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.

Any idea what I am doing wrong?
Left by Felix on Mar 15, 2009 5:04 PM

# re: Oracle Update with Join
Requesting Gravatar...
Hi Felix,

Without seeing the actual query, it's near impossible to troubleshoot.

However, you could check a couple of things. First, verify that the table you are updating has a PK. Also, I don't think you can update any part of the key using this method.

Unfortunately, there are some limitations with this syntax. But Oracle providers other syntactic options.

Good Luck.
Left by Will on Mar 16, 2009 1:52 AM

# re: Oracle Update with Join
Requesting Gravatar...
Great Job! You just made mine easier.
Left by Mark on Mar 19, 2009 5:22 PM

# re: Oracle Update with Join
Requesting Gravatar...
Hi,

I have a same problem while updating the table. Here is the query used in sql server but am not able to achive the same in oracle. Your help is appreciated. Thanks

update top(100) a
set a.fng_calc_amt=(IsNull(b.rate_per_min,IsNull(c.rate_per_min,0))*IsNull(fng_duration,0))/60 '
from
ref.dbo.reference_tbl a
left outer join SA01.ref.dbo.mea_req_1967 b
on a.fng_incoming_operator = b.name
and IsNull(a.fng_npl_number_type,'Unknown') = b.moneta_cns_type
and case when datepart(dw,a.fng_start_dttm_normalized)=7
or datepart(dw,a.fng_start_dttm_normalized)=1
then 'Weekend' else 'Weekday' end = b.day_type
and case when a.fng_call_direction=2 then 'IN' else 'OUT' end = b.call_direction
and datepart(hh,a.fng_start_dttm_normalized) = b.hour_of_day
and b.mer_from_dttm=convert(datetime,convert(varchar(7),DATEADD(month,-1,GETDATE()),102)'.01',102)
left outer join SA01.ref.dbo.mea_req_2000 c
on IsNull(a.fng_npl_number_type,'Unknown') = c.moneta_cns_type
and c.mer_from_dttm=convert(datetime,convert(varchar(7),DATEADD(month,-1,GETDATE()),102)'.01',102)
where ((a.fng_exchange_id_normalized = 'ASDSSP2'
or a.fng_exchange_id_normalized = 'RTDSSP2'))
Left by anup on May 06, 2009 10:53 PM

# re: Oracle Update with Join
Requesting Gravatar...
ty,ty,ty
update on remote table via DB link now lasts just 3 second instead of 5 minutes.
Left by Goran on Jun 19, 2009 3:06 AM

# re: Oracle Update with Join
Requesting Gravatar...
There is better solutions:

http://forums.oracle.com/forums/message.jspa?messageID=3644458
Left by dotAge on Jul 24, 2009 2:07 AM

# re: Oracle Update with Join
Requesting Gravatar...
@dotAge
If I am reading it correctly, the statement in the link you posted is different. It will update all of the rows in the table.

The purpose of my statement is to only update certain records, but the condition is applied to a joined table.
Left by Will on Jul 24, 2009 6:02 PM

# re: Oracle Update with Join
Requesting Gravatar...
Very good article!
Left by dor_alte on Sep 02, 2009 6:37 AM

# re: Oracle Update with Join
Requesting Gravatar...
Amazing piece of code! It was a treat!! Great article!!
Left by Sri on Sep 24, 2009 9:07 AM

# re: Oracle Update with Join
Requesting Gravatar...
great article :)
i keep trying to optimize some updates of rthe last week, but I keep getting that nasty ORA-01779 error with basically everything. I allready added a PK to the updated table, and still no luck :(
(my toad has just frozen trying to do something, so I can't give you the code, sorry)
Left by mojzis on Oct 05, 2009 11:20 AM

# re: Oracle Update with Join
Requesting Gravatar...
Nice Article !
Solved my problem in seconds.
Thanks!
Left by Gaurav on Oct 21, 2009 7:00 PM

# re: Oracle Update with Join
Requesting Gravatar...
Good solution, appreciate it.
Left by Pankaj on Dec 02, 2009 4:26 PM

# re: Oracle Update with Join
Requesting Gravatar...
Nice article!
It solved my problem tooo.
Left by Laxman on Dec 09, 2009 3:54 AM

# re: Oracle Update with Join
Requesting Gravatar...
Hi,

I've got nearly the same problem... I'd like to "translate" this MS-SQL statement :

1: update employee_bonus

2: set bonus = e.bonus_value

3: from employee_bonus b

4: inner join employees e on b.employee_id = e.employee_id

5: where e.bonus_eligible = 'N'

in "oracle" language... I mad a lot of tests but all failed...

Does anybody has idea ?

(I work on Oracle 10g)

Thx
Left by Xavier on Dec 29, 2009 10:37 AM

# re: Oracle Update with Join
Requesting Gravatar...
Hi,

I've got nearly the same problem... I'd like to "translate" this MS-SQL statement :

1: update employee_bonus

2: set bonus = b.bonus_value

3: from employee_bonus b

4: inner join employees e on b.employee_id = e.employee_id

5: where e.bonus_eligible = 'N'

in "oracle" language... I mad a lot of tests but all failed...

Does anybody has idea ?

(I work on Oracle 10g)

Thx
Left by Xavier on Dec 29, 2009 10:39 AM

# re: Oracle Update with Join
Requesting Gravatar...
Doesn't work for me :
update (select srhqt00.* from srhqt00 inner join (select trunc(sysdate)-1 dte from srhqt00) b on trunc(date_creation)= b.dte)c
set c.statut = 'A';

01732. 00000 - "data manipulation operation not legal on this view"

Oracle 10gR2
Left by Richard on Mar 18, 2010 5:34 AM

# re: Oracle Update with Join
Requesting Gravatar...
I get the following error on Oracle 11gR1. The inner select does result in 53 rows, and the table being updated DOES have a primary key.

Error starting at line 10 in command:
UPDATE (
SELECT p1.ExtendedAttrs, p2.Name
FROM devowner2.Party p1
INNER JOIN devowner1.Party p2 ON p1.objectid = p2.objectid
WHERE p1.phone2 IS NOT NULL
AND p2.Name IS NOT NULL) t
SET t.ExtendedAttrs = t.Name
Error at Command Line:16 Column:4
Error report:
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
Left by Phil on Apr 08, 2010 11:10 AM

# re: Oracle Update with Join
Requesting Gravatar...
@Phil
The problem is that Oracle cannot guarantee that a single value will be found to set the ExtendedAttrs. No knowing your table structure, my first guess would be that Party.objectid does not have unique constraint on it. This would lead to potentially multiple Names identified for a single ExtendedAttrs.

@Mike & Phil
It doesn't matter if you have a primary key. What matters is if you have a unique index for your "lookup" column.

@Richard
This is a different problem entirely. You need to take a look at your view and see what parts of it can be updated. you may have to add an update trigger to the view itself.
Left by Will on Apr 10, 2010 6:09 AM

# re: Oracle Update with Join
Requesting Gravatar...
nice & VERY userful post.
Thank you very much
Left by andrew on Aug 18, 2010 4:36 AM

# re: Oracle Update with Join
Requesting Gravatar...
This command is excellent,
muchas gracias,
jlm
Left by Jose Luis on Sep 29, 2010 10:46 AM

# re: Oracle Update with Join
Requesting Gravatar...
Hi,
It is fantastic query and also you made my days easier. It solved my problem to some extent but i want to know can we update multiple values at the same time using a single query.

Thanks a bunch
Left by yogita mishra on Dec 20, 2010 5:38 AM

# re: Oracle Update with Join
Requesting Gravatar...
Yogita, look through the comments there is an example of updating multiple fields.
Left by Will on Dec 20, 2010 11:46 AM

# re: Oracle Update with Join
Requesting Gravatar...
I have added a new column to an existing sales_order table to store the total value of the order. I am trying to update this with the sum of the quantity by price of each corresponding row on the item table, but having problems due to group function. Any ideas?
Left by JJ on Feb 16, 2011 7:29 AM

# re: Oracle Update with Join
Requesting Gravatar...
@JJ you may want a different kind of update. If this doesn't work for you ... comment with the statement you are trying to run...

set feedback off;
create table sales_order
(
order_id numeric(10,0),
total numeric(8,2)
);

create table order_item
(
order_id numeric(10,0),
quantity numeric(10,0),
price numeric(8,2)
);

insert into sales_order (order_id) values (1);
insert into sales_order (order_id) values (2);
insert into sales_order (order_id) values (3);

insert into order_item (order_id, quantity, price) values (1,1,15.0);
insert into order_item (order_id, quantity, price) values (1,2,10.0);
insert into order_item (order_id, quantity, price) values (1,3, 5.0);
insert into order_item (order_id, quantity, price) values (2,1,15.0);
insert into order_item (order_id, quantity, price) values (2,2,15.0);
insert into order_item (order_id, quantity, price) values (2,2, 5.0);
insert into order_item (order_id, quantity, price) values (2,5,10.0);
insert into order_item (order_id, quantity, price) values (3,3,15.0);

update sales_order
set total = (select sum(quantity*price) from order_item where order_id = sales_order.order_id);

select * from sales_order;

drop table sales_order;
drop table order_item;

ORDER_ID TOTAL
---------------------- ----------------------
1 50
2 105
3 45
Left by Will on Feb 16, 2011 8:40 AM

# re: Oracle Update with Join
Requesting Gravatar...
Thanks, second article in google, just what I was looking for

David
Left by David on Mar 01, 2011 6:26 AM

# re: Oracle Update with Join
Requesting Gravatar...
Thank you!!!!
Left by shishir on Apr 07, 2011 9:50 PM

# re: Oracle Update with Join
Requesting Gravatar...
Fantastic Article
Left by Ritesh Singh on May 14, 2011 6:05 AM

# re: Oracle Update with Join
Requesting Gravatar...
Really good blog. I didn't notice until now that we can use from in update statement in such an effective way.

Thanks!
Left by Pritpalsingh on Jul 29, 2011 6:16 PM

# re: Oracle Update with Join
Requesting Gravatar...
Thanks for your share!
Left by emily on Sep 06, 2011 8:20 AM

# re: Oracle Update with Join
Requesting Gravatar...
Thanks, solved my problem in second
Left by Elyi on Apr 10, 2012 5:40 AM

# re: Oracle Update with Join
Requesting Gravatar...
Nice solution !

It solved our problem. Our request was 2 times longer before applying your trick !
Left by Goundy on May 09, 2012 10:43 AM

Your comment:
 (will show your gravatar)


Copyright © Will Smith | Powered by: GeeksWithBlogs.net | Join free