Home » SQL & PL/SQL » SQL & PL/SQL » update a table with values from a different table
update a table with values from a different table [message #212472] Fri, 05 January 2007 08:36 Go to next message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Hi,

Could someone please help me with the following query:

I have 2 tables T1 and T2
T1 (f1, f2, f3) f1 and f2 are keyfields and f3 is a flag
T2 (F1, F2, F3) F1 and F2 are keyfields and F3 is a flag

The tables have one to many relationship i.e for 1 record in T1 there are many records in T2).

How do I update the records in T2 to set F3 = FALSE when f3 = FALSE in T1?

Thanks
Raoul

Re: update a table with values from a different table [message #212481 is a reply to message #212472] Fri, 05 January 2007 09:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
The tables have one to many relationship i.e for 1 record in T1 there are many records in T2).

What is the join condition?
Is it F1 and F2?
If so, What is the use of having many rows in T2 for a F1 and F2? Anyway you want to update F3 to FALSE for all the rows. Won't they become duplicate rows, if you do that?

By
Vamsi
Re: update a table with values from a different table [message #212487 is a reply to message #212472] Fri, 05 January 2007 09:32 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Quote:

create table foo1 (k1 varchar2(30), k2 varchar2(30), f3 varchar2(1));
create table foo2 (k1 varchar2(30), k2 varchar2(30), f3 varchar2(1));
insert into foo1 (k1,k2,f3)
select owner,object_type,'T' from dba_objects
group by owner,object_type;

insert into foo2 (k1,k2,f3)
select owner,object_type , 'T' from
(
select owner,object_type , row_number()
over ( partition by owner,object_type order by null) as rn from dba_objects
)
where rn < 4;
commit;

create unique index fooidx1 on foo1 (k1,k2);

alter table foo1 add constraint foo1pk
primary key (k1,k2) using index ;

update foo1 set f3='F' where k1='MGPH' and k2='TABLE';
update foo1 set f3='F' where k1='SYSTEM' and k2='PROCEDURE';
update foo1 set f3='F' where k1='SYS' and k2='PACKAGE';
comit;

=============================

The above steps just created the data. Use your data instead.
=============================
update (
select
t1.f3 frm , t2.f3 dst
from foo1 t1 ,foo2 t2
where t1.f3='F'
and t1.k1=t2.k1
and t1.k2=t2.k2
)
;

Commit;


As long as foo2 does not have k1,k2 as its PK/UK , this will work.




Re: update a table with values from a different table [message #212488 is a reply to message #212472] Fri, 05 January 2007 09:33 Go to previous messageGo to next message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Sorry I forgot to mention that T2 has 3 keyfields but the join will be on the first 2 fields.

Thanks
Re: update a table with values from a different table [message #212490 is a reply to message #212488] Fri, 05 January 2007 09:38 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
Ouch !!!! Did not copy paste the complete update SQL.




update (
select
t1.f3 frm , t2.f3 dst
from foo1 t1 ,foo2 t2
where t1.f3='F'
and t1.k1=t2.k1
and t1.k2=t2.k2
)
set dst=frm ;

7 rows updated.

select * from foo2 where f3='F';

K1 K2 F
------------------------------ ------------------------------ -
MGPH TABLE F
MGPH TABLE F
MGPH TABLE F
SYS PACKAGE F
SYS PACKAGE F
SYS PACKAGE F
SYSTEM PROCEDURE F

7 rows selected.

=============


T2 can take dupes on the k1,k2 because you have a 3 column key And you are good to go.

Srini



Re: update a table with values from a different table [message #212497 is a reply to message #212472] Fri, 05 January 2007 09:55 Go to previous messageGo to next message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Many thanks for your reply.

When I run the script I get the error message



ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table

line 9 is set dst=frm ;

Thanks
Re: update a table with values from a different table [message #212522 is a reply to message #212472] Fri, 05 January 2007 11:57 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Do you have the PK on the foo1 , the source table ?

Srini
Re: update a table with values from a different table [message #212539 is a reply to message #212497] Fri, 05 January 2007 13:45 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Can it be that the table you are updating is ectually a view?
If that is the case you can not do the update.

You then probably have to update the tables of wich the view is constructed.

Best Regards
Martijn
Previous Topic: populating data from temp tables
Next Topic: Select column Dynamically
Goto Forum:
  


Current Time: Sat Dec 10 20:26:09 CST 2016

Total time taken to generate the page: 0.09146 seconds