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]

Re: Help on Oracle Update statement

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" <don_leeNO_aa_S PAM@telus.netwr ote in message
news:q0j290t74e annlodc70rn7ffe nafudnpu8@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]

Re: Help on Oracle Update statement

Don <don_leeNO_aa_S PAM@telus.netwr ote in message news:<q0j290t74 eannlodc70rn7ff enafudnpu8@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]

Re: Help on Oracle Update statement


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


Mark.Powell@eds .com (Mark D Powell) wrote:

>Don <don_leeNO_aa_S PAM@telus.netwr ote in message news:<q0j290t74 eannlodc70rn7ff enafudnpu8@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]

Re: Help on Oracle Update statement

Don <don_leeNO_aa_S PAM@telus.netwr ote in message news:<dbs390538 o5kcbl72q5ukasi i0ebi6lvsf@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 ...
>
>
Mark.Powell@eds .com (Mark D Powell) wrote:
>

Don <don_leeNO_aa_S PAM@telus.netwr ote in message news:<q0j290t74 eannlodc70rn7ff enafudnpu8@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 --