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

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
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks!

/Stefan

"DA Morgan" <damorgan@psoug.org> skrev i meddelandet
news:1176824299.52428@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
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

On Apr 17, 9:36 pm, "fgsdfgsdf" <sdfg...@nospam.se> wrote:
> Thanks!
>
> /Stefan
>
> "DA Morgan" <damor...@psoug.org> skrev i meddelandetnews:1176824299.52428@bubbleator.drizzl e.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...@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;