Nice article!
It solved my problem.

thank you

Cheers, saved me some minutes...:-)

Thanks a bunch, this saved me a ton of time and the nasty prospect of having to write some cursor-based nonsense.

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...

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;

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

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!

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!

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?

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.

Great Job! You just made mine easier.

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'))

ty,ty,ty
update on remote table via DB link now lasts just 3 second instead of 5 minutes.

There is better solutions:

http://forums.oracle.com/forums/message.jspa?messageID=3644458

@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.

Very good article!

Amazing piece of code! It was a treat!! Great article!!

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)

Nice Article !
Solved my problem in seconds.
Thanks!

I really appreciate your work to this site.So thanks for it.I hope you can continue this type of hard work to this site in future also..Your work is really remarkable.