But it is going to update the table I don't want to do that. Later on performance may be the issue. I am playing with more than 10million worth of data.
On Wed, Sep 1, 2010 at 12:56 PM, Trail <andrew.tr...@gnb.ca> wrote: > Hi folks, > > I actually read this question differently, just looking at: > > "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 " > > I am likely oversimplifying, but I would simply use and NVL statement > to update the column to itself (no change) when the subquery returns > NULL. The drawback here, is that every row will be updated. > Depending on the table, rollback or locking might be an issue (if it > is a problem, using an exists clause for the same subqueries might be > an idea). > > For what it's worth, here's the syntax. > > UPDATE TABLE_A A > SET A.COL1 = NVL((SELECT B.COL1 FROM TABLE B WHERE A.COL_PK = > B.COL_PK),A.COL1); > > > -Andrew. > > > > On Sep 1, 2: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- Hide quoted text - > > > > - Show quoted text - > > -- > 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
