Help on Oracle Update statement

[Don]

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..

[Chris Leonard]

Don,

Try something like this:

UPDATE TABLE1
SET FIELD1 =
(SELECT FIELD2 FROM TABLE2
WHERE TABLE2.FIELD2 = TABLE2.FIELD1)
....
--
Cheers,
Chris

_______________ _______________ _____

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, OCP, CIW
_______________ _______________ _____

"Don" <do************ ***@telus.net> wrote in message
news:q0******** *************** *********@4ax.c om...

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..

[Mark D Powell]

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 --

[Don]

And yes, using alias/and subquery is working .. thanks for all who
response quickly..

Now my next question is : would the alias works in multiple nest
level? For example :

UPDATE TABLE1 T1
set T1.field1 = ( select T2.field1 from TABLE2 T2 where T2.field2 = (
select T3.field3 from TABLE3 T3 where T3.field1 = T1.field1 )

Thanks again ...
Ma*********@eds .com (Mark D Powell) wrote:

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 --

[Mark D Powell]

Don <do************ ***@telus.net> wrote in message news:<db******* *************** **********@4ax. com>...

And yes, using alias/and subquery is working .. thanks for all who
response quickly..

Now my next question is : would the alias works in multiple nest
level? For example :

UPDATE TABLE1 T1
set T1.field1 = ( select T2.field1 from TABLE2 T2 where T2.field2 = (
select T3.field3 from TABLE3 T3 where T3.field1 = T1.field1 )

Thanks again ...
Ma*********@eds .com (Mark D Powell) wrote:

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 --

Yes, you can nest subqueries including coordinated sub-queries though
in my experience the second subquery would be coordinated to the first
subquery which in turn is coordinated to the driving query. In your
example you would probably be better off to combine the two subqueries
into a join.

HTH -- Mark D Powell --