Don <do***************@telus.net> wrote in message news:<q0********************************@4ax.com>. ..
Hi,
I am moving from Sybase to Oracle and I used to be able to do update
statement like this in Sybase:
UPDATE TABLE1
SET T1.field1 = T2.field2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.field2 = T2.field2
AND ....
but in Oracle it is not valid. Does anyone know how to convert it to
Oracle?
Thanks in advance..
One form is:
update table1 t1
set t1.field1 = ( select t2.field2
from table2 t2
where t2.field2 = t1.field1 ...
)
where exists ( select 'X' from table2 t3
where t3.field2 = t1.field1 ...other cond ...
);
The first subquery gets the value from the other table where the
values match while the where clause on the update prevent updating the
column to null for non-matching rows.
HTH -- Mark D Powell --