Hi Swaroop, Thanks for the feedback. My intention was not to solve your problem entirely but to illustrate a concept that might get you closer to a solution and to provide a piece of Oracle knowledge that would be of interest to the mailing list community in general.
A join view *will* work with tables with different keys... even composite keys. As I mentioned in my solution, you just need to make sure that the keys involved are identified as primary key constraints. Rich Pascual Database Programmer IT Systems Management U.C. Berkeley On Wed, Sep 1, 2010 at 11:31 AM, swaroop gowda <swaroop.t...@gmail.com>wrote: > 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 > -- 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
