Richard, This looks nice but this works fine if the primary key in all the table is same. I am trying to join with different keys as all 3 tables has composite primary keys.
On Wed, Sep 1, 2010 at 12:42 PM, Richard Pascual <richg...@gmail.com> wrote: > Hi Swaroop, > > Using your example table structures, here's how I figured out how to > generate a working update statement: > > The key is that you need to make sure that your col_pk (I am assuming it is > a primary key column) is also declared as a primary key constraint. > > The limitation of this solution (using a "join view") is that you *cannot* > update references to columns from "table_b" and "table_c" but the update > statement will let you change columns from "table_a"... which looks ok > anyways from the nature of your request. > > - Rich > > -- BEGIN solution here... > > -- Create table > create table TABLE_A > ( > COL_PK NUMBER not null, > COL1 VARCHAR2(10), > COL2 VARCHAR2(10) > ); > > -- Create table > create table TABLE_B > ( > COL_PK NUMBER not null, > COL1 VARCHAR2(10) > ); > > -- Create table > create table TABLE_C > ( > COL_PK NUMBER not null, > COL2 VARCHAR2(10) > ); > > -- Create/Recreate primary, unique and foreign key constraints > alter table TABLE_C > add constraint TABLE_C_PK primary key (COL_PK) > > -- Create/Recreate primary, unique and foreign key constraints > alter table TABLE_B > add constraint TABLE_B_PK primary key (COL_PK) > > -- Create/Recreate primary, unique and foreign key constraints > alter table TABLE_A > add constraint TABLE_A_PK primary key (COL_PK) > > > insert into table_a ( col_pk, col1, col2 ) values ( 1, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 2, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 3, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 4, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 5, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 6, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 7, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 8, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 9, null, null ); > insert into table_a ( col_pk, col1, col2 ) values ( 10, null, null ); > commit; > > insert into table_b ( col_pk, col1 ) values ( 1, 'BLUE' ); > insert into table_b ( col_pk, col1 ) values ( 2, 'GREEN' ); > insert into table_b ( col_pk, col1 ) values ( 3, 'RED' ); > insert into table_b ( col_pk, col1 ) values ( 8, 'YELLOW' ); > insert into table_b ( col_pk, col1 ) values ( 9, 'ORANGE' ); > commit; > > insert into table_c ( col_pk, col2 ) values ( 2, 'A' ); > insert into table_c ( col_pk, col2 ) values ( 4, 'B' ); > insert into table_c ( col_pk, col2 ) values ( 5, 'C' ); > insert into table_c ( col_pk, col2 ) values ( 6, 'D' ); > insert into table_c ( col_pk, col2 ) values ( 8, 'E' ); > commit; > > select * from table_a > > -- update query > > update ( > > select a.col_pk as pka, > a.col1 as col1, > a.col2 as col2, > b.col_pk as pkb, > b.col1 as col1b, > c.col_pk as pkc, > c.col2 as col2c > from table_a a > left outer join table_b b > on a.col_pk = b.col_pk > left outer join table_c c > on a.col_pk = c.col_pk > > ) set col1 = col1b, col2 = col2c; > > commit; > > -- table comparisons > > SQL> select * from table_a > 2 / > > COL_PK COL1 COL2 > ---------- ---------- ---------- > 1 BLUE > 2 GREEN A > 3 RED > 4 B > 5 C > 6 D > 7 > 8 YELLOW E > 9 ORANGE > 10 > > 10 rows selected > > SQL> select * from table_b > 2 / > > COL_PK COL1 > ---------- ---------- > 1 BLUE > 2 GREEN > 3 RED > 8 YELLOW > 9 ORANGE > > SQL> select * from table_c > 2 / > > COL_PK COL2 > ---------- ---------- > 2 A > 4 B > 5 C > 6 D > 8 E > > SQL> > > > > > Rich Pascual > Database Programmer > IT Systems Management > U.C. Berkeley > > > On Wed, Sep 1, 2010 at 9:55 AM, swaroop gowda <swaroop.t...@gmail.com>wrote: > >> Can we arite update statement like below, >> UPDATE TABLE_A A >> SET A.COL1 = (SELECT B.COL1 FROM TABLE B WHERE A.COL_PK = B.COL_PK), >> A.COL2 = (SELECT C.COL2 FROM TABLE C WHERE A.COL_PK = C.COL_PK) >> >> I tried for single like below but I am facing one issue. The issue is when >> ever there is a match it is updating whole TABLE_A A.COL1 to NULL. >> Basically it should update when there is a match else if the subquery >> returns no data should not update anything. Please let me know how can I do >> >> UPDATE TABLE_A A >> SET A.COL1 = (SELECT B.COL1 FROM TABLE B WHERE A.COL_PK = B.COL_PK); >> >> -- >> Thanks & Regards >> Swaroop Thailuru Swamy >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to Oracle-PLSQL@googlegroups.com >> To unsubscribe from this group, send email to >> oracle-plsql-unsubscr...@googlegroups.com >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- Thanks & Regards Swaroop Thailuru Swamy -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
