| migration of SYBASE to ORACLE - UPDATE command [message #373484] |
Thu, 19 April 2001 01:12 |
Rod Keeble
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
Migration in progress and I have come across the difference in the UPDATE command - ORACLE forces the use of a sub select which makes the SQL quite cumbersome (compared with Sybase/SQL Server) to read/write/translate.
Does anyone have a neater solution or know of tools to auto convert Sybase/SQL code to Oracle ??
T = Table
F = Field
Therefore T1F3 = 3rd field in table 1
ORACLE
---------------------------------------------
update T1 i3
set (i3.T1F1,i3.T1F2,i3.T1F3,i3.T1F4)
= (select s3.T2F1,1,s3.T2F2,999
from T2 s3
where T2F3=i3.T1F5)
where i3.T1F5 in (select i2.T1F5
from T1 i2,T2 s2
where s2.T2F2 < i2.T1F3
and i2.T1F3 <= SysDate
and i2.T1F5 = s2.T2F3)
SYBASE/SQL Server
---------------------------------------------
UPDATE T1
SET T1F1 = 1,
T1F2 = T2F1,
T1F3 = T2F2,
T1F4 = 999
From T2, T1
WHERE ((T2F2 < T1F3 )
and (T1F3 <= SYSDATE
and (T1F5 = T2F3))
---------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
