I agree with you. Thank you Andrew. On Wed, Sep 1, 2010 at 1:27 PM, Trail <andrew.tr...@gnb.ca> wrote:
> If you want to limit the rows updated on a large table, I guess I > would look at one of a couple options then ... > > 1) UPDATE TABLE_A A > SET A.COL1 = NVL((SELECT B.COL1 FROM TABLE B WHERE A.COL_PK = > B.COL_PK),A.COL1), > A.COL2 = NVL((SELECT C.COL2 FROM TABLE C WHERE A.COL_PK = > C.COL_PK),A.COL2) > where exists (SELECT 'Found B table record' FROM TABLE B WHERE > A.COL_PK = B.COL_PK) > or exists (SELECT 'Found C table record' FROM TABLE C WHERE > A.COL_PK = C.COL_PK) > > Downside - it will update col1 when only col2 is found and vice > versa. The data will not change in those cases, but this might not be > exactly what you are looking for either... > > 2) Performance (if you are looking at really large tables, with many > updates) - this is quite a bit different... > > (obviously, include any other columns that exist.) > > create table new_table as (select NVL((SELECT B.COL1 FROM TABLE B > WHERE A.COL_PK = B.COL_PK),A.COL1) > ,NVL((SELECT C.COL2 FROM TABLE C > WHERE A.COL_PK = C.COL_PK),A.COL2) > from table_a A) > > drop old table > > rename new_table to old table. > > Anyway, just thought I'd throw the ideas out there. > > -A. > > > On Sep 1, 3:06 pm, swaroop gowda <swaroop.t...@gmail.com> wrote: > > 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- 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