Re: Help! Sybase SQL to Oracle...

From: mark rostron <markro2_at_atlantis.rosemount.com>
Date: 1996/01/17
Message-ID: <1996Jan17.162350.23817_at_rosevax.rosemount.com>#1/1

Yep. The syntax of the update statement is different across SYBASE and ORACLE. In ORACLE, you have a construct called a cursor in ORACLE pl/sql (the equivalent to transactSQL in sybase) which you can use to emulate this.

Apologies for any errors in the sybase stuff on account of it's been a little while:

eg create table a(i integer,a1 integer,a2 integer);
   create table b(i integer,b1 integer,b2 integer);
   create table c(i integer,c1 integer,c2 integer);

Field i is the foreign key relating tables a,b,c.

now, say you want to update c from a selection across (a,b).

SYBASE syntax:

---

update c
set c1 = a.a1

    c2 = b.b2
from a,b
where a.i = c.i
and b.i = c.i
go

ORACLE syntax:

---

declare
  cursor ab_cursor
  is
  select a.i id, a.a1 afield, b.b1 bfield   from a,b
  where a.i = b.i;
begin
  for ab_rec in ab_cursor
  loop
    update c
    set

      c1=ab_rec.afield,
      c2=ab_rec.bfield
    where c.id = ab_rec.id;         

  end loop;
end;
/

ref manual on using cursors, cursor loops, also in the delete/update embedded statements, also look at 'for update of' and 'where current of' clauses in cursor syntax.

mr Received on Wed Jan 17 1996 - 00:00:00 CET