The inline view is a beautiful solution that does not seem to work. I
get the following output from SQL*PLUS:
1 update
2 (select r.file1, s.ftp_file
3 FROM status r, values s
4 WHERE r.name = s.name and
5 r.date = s.stamp and
6 instr(s.ftp_file,'A.ZIP') > 0) u
7* set u.file1 = u.ftp_file
SQL> /
set u.file1 = u.ftp_file
*
ERROR at line 7:
ORA-01779: cannot modify a column which maps to a non key-preserved
table
Any ideas?
gameBuddah
In article <954177917.13500.1.pluto.d4ee154e_at_news.demon.nl>,
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> 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.
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Mar 27 2000 - 14:10:41 CST
