ORACLE SQL - Is this script okay?

Sleepy

Hi all, I'm trying to "oracleize" an SQL statement for one of the guys in the office here and I'm wondering if the following is okay in Oracle. I know it works fine in SQL Server.

update table1
set table1.period = table2.period
from table1, table2
where table1.client = table2.client 
and table1.trans_no = table2.fatr_trans_nbr
and table1.status = '5'

client and trans_no would be the primary key on table1 with client and fatr_trans_nbr being the corresponding foreign key in table2.

Zaph0d

I don't believe you can use a FROM clause in an Oracle UPDATE statement the way you can in SQL Server.

UPDATE table1 t1
SET t1.period= 
(SELECT t2.period from table2 t2 WHERE t2.client=t1.client
and t1.trans_no = t2.fatr_trans_nbr
and t1.status = '5')

Seth Brundle

try

update table1, table2
set table1.period = table2.period
where table1.client = table2.client 
and table1.trans_no = table2.fatr_trans_nbr
and table1.status = '5'

What datatype is table1.status?

Sleepy

It's an integer...

My real problem with this is that I don't have an Oracle DB to test this on.

Seth Brundle

omit the single quotes!

Like yourself, I haven't access to oracle on this PC.

Seth Brundle

Managed to test my script - doesn't work - I think that may be for MySQL

Zaph0d

kbannon wrote:

try

update table1, table2
set table1.period = table2.period
where table1.client = table2.client 
and table1.trans_no = table2.fatr_trans_nbr
and table1.status = '5'

Oracle doesn't allow two table names between the UPDATE and SET keywords.

Plankmonkey

This site will show you:

http://www.techonthenet.com/index.php