OraFAQ Forum: SQL & PL/SQL » Update multiple rows with multi join

Home » SQL & PL/SQL » SQL & PL/SQL » Update multiple rows with multi join (oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update multiple rows with multi join [message #645390]

Fri, 04 December 2015 16:53

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Team,

I am using below query to update values in table which has multiple joins but i am getting errors.

query :

update (select vpt.dor_mas_id as vpt_id , vm.dor_mas_id as vm_id from ven_pe_tr vpt
  join gpn l on vpt.case_nbr = l.tc_gpn_id
  join ven_ma vm on l.business_partner_id = vm.vendor_id
  where
   vpt.stat_code = '10'
  and vpt.dor_mas_id <> vm.dor_mas_id) set vpt_id=vm_id;

Errors:

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.

Can someone please guide me on this

Report message to a moderator

Re: Update multiple rows with multi join [message #645391 is a reply to message #645390]

Fri, 04 December 2015 17:23

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

i have written another version of query but it is not giving expected results. After execution of this query i expected vpt.dor_mas_id = vm.dor_mas_id

set serveroutput on;

BEGIN
For i in (select vpt.dor_mas_id as vpt_id , vm.dor_mas_id as vm_id from ven_pe_tr vpt
  join gpn l on vpt.case_nbr = l.tc_gpn_id
  join ven_ma vm on l.business_partner_id = vm.vendor_id
  where
   vpt.stat_code = '10'
  and vpt.dor_mas_id <> vm.dor_mas_id) 
LOOP
update ven_pe_tr set dor_mas_id = i.vm_id where dor_mas_id = i.vpt_id;
-- select vendor_master_id into vid from vend_perf_tran where vendor_master_id = i.vm_id;
--dbms_output.put_line( 'vid = ' || vid);
END LOOP;
END;

Report message to a moderator

Re: Update multiple rows with multi join [message #645393 is a reply to message #645391]

Fri, 04 December 2015 17:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Never do in PL/SQL that which can be done in plain SQL.

>update ven_pe_tr set dor_mas_id = i.vm_id where dor_mas_id = i.vpt_id;
The only time when UPDATE "fires" is where dor_mas_id = i.vpt_id to begin with.
This UPDATE leaves all rows unchanged.

Report message to a moderator

Re: Update multiple rows with multi join [message #645395 is a reply to message #645393]

Fri, 04 December 2015 18:07

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Ok sure, How to do that in plain SQL? i tried couple of things, below query is giving errors. Appreciate your help

query :

update (select vpt.dor_mas_id as vpt_id , vm.dor_mas_id as vm_id from ven_pe_tr vpt
  join gpn l on vpt.case_nbr = l.tc_gpn_id
  join ven_ma vm on l.business_partner_id = vm.vendor_id
  where
   vpt.stat_code = '10'
  and vpt.dor_mas_id <> vm.dor_mas_id) set vpt_id=vm_id;

Errors:

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.

Report message to a moderator

Re: Update multiple rows with multi join [message #645396 is a reply to message #645395]

Fri, 04 December 2015 18:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We don't have your tables.
We don't have your data.
We don't have your requirements.

We can't provide working SQL unless & until we have all the above.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Update multiple rows with multi join [message #645397 is a reply to message #645393]

Fri, 04 December 2015 18:16

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

what is the wrong here ?, Yes i am expecting when where condition matches then only update.

Quote:

Never do in PL/SQL that which can be done in plain SQL.

>update ven_pe_tr set dor_mas_id = i.vm_id where dor_mas_id = i.vpt_id;
The only time when UPDATE "fires" is where dor_mas_id = i.vpt_id to begin with.
This UPDATE leaves all rows unchanged.

Report message to a moderator

Re: Update multiple rows with multi join [message #645398 is a reply to message #645397]

Fri, 04 December 2015 18:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ignore what I posted.
I mis-read the code.

Report message to a moderator

Re: Update multiple rows with multi join [message #645399 is a reply to message #645398]

Fri, 04 December 2015 18:26

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

ok, I am trying to update dor_mas_id in ven_pe_tr table with values captured in i but after update i am not seeing vpt.dor_mas_id = vm.dor_mas_id. Am i missing anything here. Sure i will provide all tables with data, just incase if anything obviously looking wrong.

set serveroutput on;

BEGIN
For i in (select vpt.dor_mas_id as vpt_id , vm.dor_mas_id as vm_id from ven_pe_tr vpt
  join gpn l on vpt.case_nbr = l.tc_gpn_id
  join ven_ma vm on l.business_partner_id = vm.vendor_id
  where
   vpt.stat_code = '10'
  and vpt.dor_mas_id <> vm.dor_mas_id) 
LOOP
update ven_pe_tr set dor_mas_id = i.vm_id where dor_mas_id = i.vpt_id;

END LOOP;
END;

[Updated on: Fri, 04 December 2015 18:27]

Report message to a moderator

Re: Update multiple rows with multi join [message #645400 is a reply to message #645399]

Fri, 04 December 2015 18:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You don't know what you don't know.

> but after update i am not seeing vpt.dor_mas_id = vm.dor_mas_id.

Other sessions can NOT see uncommitted DML changes.
results depend upon who looks where for the results.

Why is no COMMIT shown?

Report message to a moderator

Re: Update multiple rows with multi join [message #645401 is a reply to message #645400]

Fri, 04 December 2015 18:40

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Actually i didnt add commit to avoid any wrong updates. With in same sql session i executed select statment before and after execution of update.

before execution
---------------
130986	130997
124920	111511
109172	111511


after execution
---------------
130997	130986
130997	130986
130997	130986

expected
--------------

130997  130997
111511  111511
111511  111511

Report message to a moderator

Re: Update multiple rows with multi join [message #645402 is a reply to message #645401]

Fri, 04 December 2015 18:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Obviously UPDATE is firing, since before & after are different.
all you have to do is to reconcile what Oracle produces with what you see & NOT your expectations.
Oracle is too dumb to lie.

Report message to a moderator