Thank you so much Richard, I got an isea from you people to solve this issue.
On Wed, Sep 1, 2010 at 2:28 PM, Richard Pascual <richg...@gmail.com> wrote: > 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 > -- 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
