Re: Update in joined tables???

From: Mike Krolewski <mkrolewski_at_rii.com>
Date: Thu, 18 Jan 2001 22:18:28 GMT
Message-ID: <947q39$ai$1@nnrp1.deja.com>

In article <947n8s$6e8$1_at_troll.powertech.no>,   "Ståle Veipe" <stale_at_veipe.com> wrote:
> I have a script that i cant get to work on oracle isnt this possible
 to do
> on oracle server?
>
> UPDATE tab1 SET tab1.field1=0 FROM tab1 LEFT OUTER JOIN tab2 ON
 (tab1.id =
> tab2.id) WHERE tab2.field2 < 1 AND tab1.field3='Debug'
>
> I can do select this way (not on oracle though). Can this be done? or
 does
> anyone hava another sollution?
>
>

update tab1 t1

    set t1.field1 = 0
where

    t1.id in ( select t1a.id

                    from tab1 t1a,
                         tab2 t2
                    where
                         t1a.id = t2.id(+)
                         and t1a.field3 = 'DEBUG'
                         and t2.field2 > 1 (+)
              )

... I cannot recall if the 'and t2.field2 > 1(+)' is correct or
                           'and t2.field2(+) > 1 ' is correct

In any case, you should be able to execute the subquery independently to test if the correct rows are being returned. You definitely can have conditions. This should return all rows in

       tab1 have 'DEBUG' in field3 and no associated row in tab2
      AND
       tab1 have 'DEBUG' in field3, are associated row in tab2 whose

field2 > 1

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/

Received on Thu Jan 18 2001 - 16:18:28 CST