Re: Update on a join

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 19 Feb 2001 20:58:59 +0100
Message-ID: <t92uk5a0aae4fe@beta-news.demon.nl>

Regretabbly this will NOT do the job as
for all instances of table1 where key doesn't exist in table2 table1.fldX will be *Nullified*

So either
 UPDATE Table1
 SET Table1.FldX =

        (SELECT Table2.FldY
         FROM Table2
        WHERE Table1.Key = Table2.Key)

where exists
(select 'x'
 from table2
where table2.key = table1.key)

or
update
(select fldX, fldY
 from table1, table2
where table1.key = table2.key)
set fldX = fldY
)
in Oracle 8i and beyond

This might not work in pl/sql however.

Regards,

Sybrand Bakker, Oracle DBA

"Javier" <jmoreno_at_hag.es> wrote in message news:x7ck6.17$bA6.1279_at_telenews.teleline.es...
> Try this:
>
> UPDATE Table1
> SET Table1.FldX = (SELECT Table2.FldY FROM Table2 WHERE Table1.Key =
> Table2.Key);
>
> This will do the job.
>
> "Dave Sutton" <dpsutton_at_marchsystems.co.uk> escribió en el mensaje
> news:96rhnl$h16$1_at_newsreaderm1.core.theplanet.net...
> > Help!!
> >
> > I need to perform an update on a single table but the information to be
 used
> > in the update comes from another table.
> >
> > In SQL server I would have used:
> >
> > UPDATE Table1
> > Set Table1.FldX = Table2.FldY
> > FROM Table1, Table2
> > WHERE Table1.Key = Table2.Key
> >
> > But ORACLE won't allow me to do this.
> >
> > Any ideas??
> >
> > Thanks.
> >
> >
> > Dave
> >
> >
>
>
Received on Mon Feb 19 2001 - 13:58:59 CST