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

Reply via email to