Re: SQL: Updates a table based on a join

A copy of this was sent to gamebuddah_at_my-deja.com (if that email address didn't require changing) On Mon, 27 Mar 2000 15:57:19 GMT, you wrote:

>I have a situation where I need to update a table (STATUS) based on
>another table (VALUES). STATUS has the following columns; name, file1,
>file2, file3, date. VALUES has the following columns: name, ftp_file,
>date; name joins to name, date joins to date. What I need to do is to
>set the values of status.file1 = values.ftp_file where status.name =
>values.name and status.date = values.date and instr(ftp_file,'A.ZIP') >
>0.
>The following select statement gives me the clientnames where this
>match occurs
>SELECT s.name
>FROM status s, values v
>WHERE s.name = v.name and
>s.date = v.date and
>instr(ftp_file,'A.ZIP') > 0;
>But I can't figure out how to turn this into an UPDATE statement.
>Please let me know if anyone has any insight.
>gameBuddah
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

If there is a unique constraint on values(name) or values(date) or values(name,date) then:

update
(SELECT s.name s_name, v.name v_name

   FROM status s, values v
  WHERE s.name = v.name
    and s.date = v.date
    and instr(ftp_file,'A.ZIP') > 0 )
set s_name = v_name
/

will do it. Else:

update status

   set name = (SELECT v.name

                 FROM values v
                WHERE status.name = v.name 
                  and status.date = v.date 
                  and instr(ftp_file,'A.ZIP') > 0 )
 where exists (SELECT v.name
                 FROM values v
                WHERE status.name = v.name 
                  and status.date = v.date 
                  and instr(ftp_file,'A.ZIP') > 0 )

will do it.

--
http://osi.oracle.com/~tkyte/  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 10:39:54 CST