OraFAQ Forum: SQL & PL/SQL » Avoid ORA-38104 - olumns referenced in the ON Clause cannot be updated

Home » SQL & PL/SQL » SQL & PL/SQL » Avoid ORA-38104 - olumns referenced in the ON Clause cannot be updated (Oracle 11g Release 2)

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

Avoid ORA-38104 - olumns referenced in the ON Clause cannot be updated [message #554024]

Wed, 09 May 2012 18:42

athensromania
Messages: 46
Registered: July 2007
Location: Valencia,Spain

Member

Hi this is my test data :

create table tab_1(c1 number,c2 number,c3 number);
create table tab_2(c1 number,c2 number,c3 number,c4 number,c5 number,c6 number);

insert into tab_1 values(1,1,1);
insert into tab_1 values(2,2,2);

insert into tab_2 values(1,1,1,3,3,3);
insert into tab_2 values(2,2,2,4,4,4);

The final result would be, update columns c1,c2,c3 from table tab_1 whith the values of columns c4,c5,c6 from tab_2 where
tab_1(c1,c2,c3) exist in tab_2(c1,c2,c3).

My first aproach was :

merge into tab_1
using (select c1,c2,c3,c4,c5,c6 from tab_2) tab_2
on (tab_1.c1=tab_2.c1 and
      tab_1.c2=tab_2.c2 and
      tab_1.c3=tab_2.c3
    )
when matched then 
  update set tab_1.c1=tab_2.c4,    
             tab_1.c2=tab_2.c5,    
              tab_1.c3=tab_2.c6;

but gets error ORA-38104.

My second aproach was :

update
(
select tab1.c1 old_1,
      tab1.c2 old_2,
      tab1.c3 old_3,
      tab2.c4 new_1,
      tab2.c5 new_2,
      tab2.c6 new_3
from tab_1 tab1,tab_2 tab2 
where tab1.c1=tab2.c1 and
      tab1.c2=tab2.c2 and
      tab1.c3=tab2.c3
) join_table
set join_table.old_1=join_table.new_1,
join_table.old_2=join_table.new_2,
join_table.old_3=join_table.new_3;

but gets error : ORA-01779

In a dirty way:


create table temp_table as
select tab1.c1,
      tab1.c2, 
      tab1.c3, 
      tab2.c4 new_1,
      tab2.c5 new_2,
      tab2.c6 new_3
from tab_1 tab1,tab_2 tab2 
where tab1.c1=tab2.c1 and
      tab1.c2=tab2.c2 and
      tab1.c3=tab2.c3;

delete from tab_1;

update temp_table
set c1=new_1,c2=new_2,c3=new_3;

insert into tab_1 select c1,c2,c3 from temp_table;

commit;

It works but I suppose that there is a better way to do it.

Thanks for your help.

Report message to a moderator

Re: Avoid ORA-38104 - olumns referenced in the ON Clause cannot be updated [message #554025 is a reply to message #554024]

Wed, 09 May 2012 19:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1  UPDATE TAB_1 T1
  2  SET (C1,C2,C3) = (SELECT C4,C5,C6
  3			FROM TAB_2 T2
  4		       WHERE T1.C1 = T2.C1
  5			 AND T1.C2 = T2.C2
  6*			 AND T1.C3 = T2.C3)
17:43:38 SQL> /

2 rows updated.

17:43:39 SQL> select * from tab_1;

	C1	   C2	      C3
---------- ---------- ----------
	 3	    3	       3
	 4	    4	       4

17:43:56 SQL>

Report message to a moderator

Previous Topic:	What should be the data type for this column (2 Merged)
Next Topic:	How to pass type object as a parameter to stored procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 30 10:38:39 CDT 2025