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
