[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
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...:-)
Gravatar
# re: Oracle Update with Join
posted by Tim
on 1/16/2009 5:39 PM
Thanks a bunch, this saved me a ton of time and the nasty prospect of having to write some cursor-based nonsense.
Gravatar
# re: Oracle Update with Join
posted by renu
on 2/17/2009 4:30 AM
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...
Gravatar
# re: Oracle Update with Join
posted by Will
on 2/17/2009 11:47 AM
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;
Gravatar
# re: Oracle Update with Join
posted by David
on 2/25/2009 9:40 AM
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
Gravatar
# re: Oracle Update with Join
posted by Will
on 2/26/2009 8:23 AM
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!
Gravatar
# re: Oracle Update with Join
posted by Will
on 2/26/2009 8:26 AM
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!
Gravatar
# re: Oracle Update with Join
posted by Felix
on 3/15/2009 5:04 PM
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?
Gravatar
# re: Oracle Update with Join
posted by Will
on 3/16/2009 1:52 AM
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.
Gravatar
# re: Oracle Update with Join
posted by Mark
on 3/19/2009 5:22 PM
Great Job! You just made mine easier.
Gravatar
# re: Oracle Update with Join
posted by anup
on 5/6/2009 10:53 PM
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'))
Gravatar
# re: Oracle Update with Join
posted by Goran
on 6/19/2009 3:06 AM
ty,ty,ty
update on remote table via DB link now lasts just 3 second instead of 5 minutes.
Gravatar
# re: Oracle Update with Join
posted by dotAge
on 7/24/2009 2:07 AM
There is better solutions:

http://forums.oracle.com/forums/message.jspa?messageID=3644458
Gravatar
# re: Oracle Update with Join
posted by Will
on 7/24/2009 6:02 PM
@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.
Gravatar
# re: Oracle Update with Join
posted by dor_alte
on 9/2/2009 6:37 AM
Very good article!
Gravatar
# re: Oracle Update with Join
posted by Sri
on 9/24/2009 9:07 AM
Amazing piece of code! It was a treat!! Great article!!
Gravatar
# re: Oracle Update with Join
posted by mojzis
on 10/5/2009 11:20 AM
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)
Gravatar
# re: Oracle Update with Join
posted by Gaurav
on 10/21/2009 7:00 PM
Nice Article !
Solved my problem in seconds.
Thanks!
Gravatar
# re: Oracle Update with Join
posted by Pankaj
on 12/2/2009 4:26 PM
Good solution, appreciate it.
Gravatar
# re: Oracle Update with Join
posted by Laxman
on 12/9/2009 3:54 AM
Nice article!
It solved my problem tooo.
Gravatar
# re: Oracle Update with Join
posted by Xavier
on 12/29/2009 10:37 AM
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
Gravatar
# re: Oracle Update with Join
posted by Xavier
on 12/29/2009 10:39 AM
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
Gravatar
# re: Oracle Update with Join
posted by Richard
on 3/18/2010 5:34 AM
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
Gravatar
# re: Oracle Update with Join
posted by Phil
on 4/8/2010 11:10 AM
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.
Gravatar
# re: Oracle Update with Join
posted by Will
on 4/10/2010 6:09 AM
@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.
Gravatar
# re: Oracle Update with Join
posted by andrew
on 8/18/2010 4:36 AM
nice & VERY userful post.
Thank you very much
Gravatar
# re: Oracle Update with Join
posted by Jose Luis
on 9/29/2010 10:46 AM
This command is excellent,
muchas gracias,
jlm
Gravatar
# re: Oracle Update with Join
posted by yogita mishra
on 12/20/2010 5:38 AM
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
Gravatar
# re: Oracle Update with Join
posted by Will
on 12/20/2010 11:46 AM
Yogita, look through the comments there is an example of updating multiple fields.
Gravatar
# re: Oracle Update with Join
posted by JJ
on 2/16/2011 7:29 AM
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?
Gravatar
# re: Oracle Update with Join
posted by Will
on 2/16/2011 8:40 AM
@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
Gravatar
# re: Oracle Update with Join
posted by David
on 3/1/2011 6:26 AM
Thanks, second article in google, just what I was looking for

David
Gravatar
# re: Oracle Update with Join
posted by shishir
on 4/7/2011 9:50 PM
Thank you!!!!
Gravatar
# re: Oracle Update with Join
posted by Ritesh Singh
on 5/14/2011 6:05 AM
Fantastic Article
Gravatar
# re: Oracle Update with Join
posted by Pritpalsingh
on 7/29/2011 6:16 PM
Really good blog. I didn't notice until now that we can use from in update statement in such an effective way.

Thanks!
Gravatar
# re: Oracle Update with Join
posted by emily
on 9/6/2011 8:20 AM
Thanks for your share!
Gravatar
# re: Oracle Update with Join
posted by Elyi
on 4/10/2012 5:40 AM
Thanks, solved my problem in second
Gravatar
# re: Oracle Update with Join
posted by Goundy
on 5/9/2012 10:43 AM
Nice solution !

It solved our problem. Our request was 2 times longer before applying your trick !

Post A Comment
Title:
Name:
Email:
Comment:
Verification: