Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!nntp2.aus1.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsmm00.sul.t-online.com!t-online.de!news.t-online.com!not-for-mail
From: Lothar Armbruester <lothar.armbruester@t-online.de>
Newsgroups: comp.databases.oracle.server
Subject: Re: Query from SQL SErver
Date: Thu, 10 Oct 2002 18:17:29 +0200
Organization: T-Online
Lines: 39
Message-ID: <PM0003ACA8F83FED4A@hades.none.local>
References: <fb04eea9.0210090923.6ed3a91a@posting.google.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: news.t-online.com 1034266838 07 2172 J3V3b4hbWAqlN 021010 16:20:38
X-Complaints-To: abuse@t-online.com
X-Sender: 320047863602-0001@t-dialin.net
User-Agent: Pineapple News 0.9.7 [PLAID BETA], BeOS (Intel) 5.0.3
X-Program-URL: http://www.beosdevelopers.org/brunsona/pineapplenews.html
X-Registered-To: Lothar Armbruster
Xref: newsfeed1.easynews.com comp.databases.oracle.server:163739
X-Received-Date: Thu, 10 Oct 2002 09:22:01 MST (news.easynews.com)

PABLO wrote:
> Hi, I am trying to migrate this query from SQL server to Oracle 9i and
> I can not find a way to solve it.
>
> Does someone have and idea of how to do it ?
>
> Thanks a lot in advance.
> Pablo
>
> UPDATE Table1
> SET FieldA = b.FieldB
> FROM ( SELECT Exp as FIledB, Exp as FieldC FROM ... WHERE.... ) As B
> WHERE Table1.FieldA <> b.FieldB
> AND   Table1.Field> >= b.fieldC

Hello Pablo,
you could try something like this:

update
   (select a.*,exp1 fieldb, exp2 fieldc
    from
       tablea a,
       ....
    where
       a.fielda<>exp1 and
       a.fielda>=exp2 and
       ...) set
   fielda=fieldb;

This only works if the primary key of tablea is also a key for the whole
join. (Have a look at the docs under "key preserved")

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la@oktagramm.de
Hauptstr. 26            | la@heptagramm.de
D-65346 Eltville        | lothar.armbruester@t-online.de