Re: UPDATE with Oracle ??? with join ???

Turkbear wrote:

> "rogers" <toto_at_club-toto.fr> wrote:
> 
> 

> 

> 

> 
> 
> update table1 t1 set col2 = (select  col2 from table2 where table2.col2 = table1.col2);
> 
> should do it, it you want every row in table1 to be updated...If not, then you need to add a qualifying statement like:
> 
> update table1 set col2 = (select  col2 from table2 where table2.col2 = table1.col2)
> where table1.col2 is NULL;
> 

In addition you can update the results of a SELECT statement. For example:

SQL> desc channels

  Name                                      Null?    Type
  ----------------------------------------- -------- ------------
  CHANNEL_ID                                NOT NULL VARCHAR2(1)
  CHANNEL_DESC                                       VARCHAR2(20)
  CHANNEL_CLASS                                      VARCHAR2(20)

SQL> select * from channels;

C CHANNEL_DESC CHANNEL_CLASS

- -------------------- -------------
S Direct Sales         Direct
T Tele Sales           Direct
C Catalog              Indirect
I Internet             Indirect
P Partners             Others

SQL> UPDATE (SELECT * FROM channels WHERE channel_desc = 'Catalog')

   2 SET channel_desc = 'X';

1 row updated.

SQL> SELECT * FROM channels;

C CHANNEL_DESC CHANNEL_CLASS

- -------------------- -------------
S Direct Sales         Direct
T Tele Sales           Direct
C X                    Indirect
I Internet             Indirect
P Partners             Others

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)