Re: MS SQL Server syntax to Oracle

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 17 Apr 2007 22:11:16 -0700
Message-ID: <1176873076.684109.170970@n76g2000hsh.googlegroups.com>

On Apr 17, 9:36 pm, "fgsdfgsdf" <sdfg..._at_nospam.se> wrote:
> Thanks!
>
> /Stefan
>
> "DA Morgan" <damor..._at_psoug.org> skrev i meddelandetnews:1176824299.52428_at_bubbleator.drizzle.com...
>
>
>
> > fgsdfgsdf wrote:
> >> Hi all
>
> >> I have this update statement written for MS SQL Server and need the same
> >> statement for Oracle (version 9).
> >> I can not find a way to do joins in an update statement in Oracle - is it
> >> possible?
>
> >> update Ver
> >> set Ver.ObjectCreatedBy = c.CreatedBy,
> >> Ver.ObjectCreated = c.Created
> >> from Ver
> >> join (
> >> select Object, CreatedBy, Created
> >> from Ver
> >> join (
> >> select Object Obj, min(created) Cre
> >> from Ver
> >> group by Object
> >> ) b on Ver.Object = b.Obj and Ver.Created = b.Cre
> >> ) c on Ver.Object = c.Object
>
> >> Thanks for your help
> >> Stefan Olofsson
>
> >www.psoug.org
> > Click on Morgan's Library
> > Click on Update
>
> > There are examples there that show how to do it.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.org- Hide quoted text -
>
> - Show quoted text -

You can also look into MERGE statement. In your case something like this

• Untested

MERGE VER
USING (Ver

          join (
            select Object, CreatedBy, Created
            from  Ver
                   join (
                        select Object Obj, min(created) Cre
                        from   Ver
                        group by Object
                        ) b on Ver.Object = b.Obj and Ver.Created =
b.Cre
            ) Ver.Object = c.Object
	) src

on (Ver.Object = src.Object )
WHEN MATCHED THEN
UPDATE SET Ver.ObjectCreatedBy = src.CreatedBy,

                     Ver.ObjectCreated   = src.Created;

Received on Wed Apr 18 2007 - 00:11:16 CDT