Re: Syntax help migrating MS SQL Server to Oracle

fgsdfgsdf wrote:
> Hi all
>
> I have the update statement written for MS SQL 2000 below and need to
> convert it to Oracle 9. It seems like Oracle does not support joins in
> update queries :(
> How can I do this in Oracle 9?
>
> Thanks in advance
> Stefan
>
> update t
> set t.SRights = case when ((f1.Id is null and f2.Id is null) or f2.Id is
> not null) then 1 else 0 end
> from GuiTmp t
> join ReadRights r on r.ObjectType = t.ObjectType and
> r.Status = t.Status
> join Access_ a on r.Access_ = a.Access_
> join ObjTypeAcc oa on oa.Access_ = a.Access_ and
> oa.ObjectType = r.ObjectType and
> oa.Cfg = r.Cfg
> left join FieldAcc f1 on oa.Id = f1.ObjectTypeAccess and
> f1.Cfg = 1 and
> f1.Rights = 1 and
> f1.Field = t.Field and
> f1.Status = t.Status
> left join FieldAcc f2 on oa.Id = f2.ObjectTypeAccess and
> f2.Cfg = 1 and
> f2.Rights in (2,3) and
> f2.Field = t.Field and
> f2.Status = t.Status
> where r.Cfg = 1 and
> t.RRights = -1

Try something similar to this:

CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

DECLARE
  trec t%ROWTYPE;
BEGIN
   trec.table_name := 'DUAL';
   trec.tablespace_name := 'NEW_TBSP';

   UPDATE t
   SET ROW = trec
   WHERE table_name = 'DUAL';

   COMMIT;
END;
/

Or update an in-line view.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org