Re: update question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 06 Apr 1999 01:06:39 GMT
Message-ID: <37095bde.1592720@192.86.155.100>

A copy of this was sent to ewong74_at_netscape.net (if that email address didn't require changing) On Mon, 05 Apr 1999 22:27:25 GMT, you wrote:

>I have the following update query that need to join 3 tables.
>update table_a a
>set (col2,col3)
>= (select col2,col3 from table_b b
> where a.col1=b.col1
> and col4 in (select col1 from table_c
> where col5=12345));
>
>There are only 1k row when retreiving from (select col1 from table_c where
>col5=12345). However, this query takes forever. I run the explain plan and
>it looks like it is doing a full scan of table_a. Is it updating every row
>of table_a? Is there another way to do it?
>

it is updating every row in table_a since there is no where clause on the update.

Fastest way to make it work:

update table_a a
set (col2,col3)
= (select col2,col3 from table_b b

   where a.col1=b.col1
   and col4 in (select col1 from table_c    where col5=12345))
where
col1 in ( select col1 from table_b );

Another way that might work and be faster would be:

SQL> create table table_a ( col1 int, col2 int, col3 int ); Table created.

SQL> create table table_b ( col1 int PRIMARY KEY, col2 int, col3 int, col4 int

);
                                     ^^^^^^^^^^^ thats a requirement

Table created.

SQL> create table table_c ( col1 int, col5 int ); Table created.

SQL> insert into table_a values ( 1, 0, 0 );
SQL> insert into table_b values ( 1, -1, -2, 5 );
SQL> insert into table_c values ( 5, 12345 );

SQL> select * from table_a;

      COL1 COL2 COL3
---------- ---------- ----------

         1 0 0

SQL> update

  2  ( select a.col1 a_col1, a.col2 a_col2, a.col3 a_col3,
  3           b.col1 b_col1, b.col2 b_col2, b.col3 b_col3
  4          from table_a a, table_b b
  5     where a.col1 = b.col1
  6           and b.col4 in ( select col1 from table_c where col5 = 12345 )

  7 )
  8 set a_col2 = b_col2, a_col3 = b_col3   9 /

1 row updated.

SQL> select * from table_a;

      COL1 COL2 COL3
---------- ---------- ----------

         1 -1 -2

As long as there is a unique or primary key on table_b, this'll work (updating the join)

>Thanks in advance!
>
>Ed
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Apr 05 1999 - 20:06:39 CDT