469,602 Members | 1,715 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

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

Jul 19 '05 #1
4 57449
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.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..

Jul 19 '05 #2
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 --
Jul 19 '05 #3
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 --


Jul 19 '05 #4
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 --
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by cricketunes | last post: by
4 posts views Thread by francis70 | last post: by
8 posts views Thread by Cherrish Vaidiyan | last post: by
11 posts views Thread by Markus Breuer | last post: by
4 posts views Thread by Don | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.