Re: Updates a table based on a join

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 27 Mar 2000 18:26:22 +0200
Message-ID: <954177917.13500.1.pluto.d4ee154e@news.demon.nl>

Either convert the join to a subquery (multiple columns ARE allowed)
> update status s

 set s.file1 =

       (select v1.ftp_file
        from values v1
        where v1.name = s.name
         and   v1.date = s.date
       and instr(ftp_file,'A.ZIP') > 0)

  where exists
(select 'x'
  from values v
  where v.name = s.name

     and v.date = s.date
     and instr(ftp_file,'A.ZIP') > 0)

or use an inline view
update
( s.file1, v.ftp_file

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

Both should work, although the latter one probably in 7.3 and beyond only.

Hth,

Sybrand Bakker, Oracle DBA

<gamebuddah_at_my-deja.com> wrote in message news:8bo0cm$779$1_at_nnrp1.deja.com...
> 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.
Received on Mon Mar 27 2000 - 10:26:22 CST