Update with Oracle DB

[saw15]

Couple of questions...

Can you update two tables within one statement? If so, can anyone provide a sample?

Can you update two fields on one table, using a join to another table within the update table? If so, can anyone provide a sample?

I would like to update two fields on one table using a join between two tables and one common field.

Thanks in advance..

 

[grega]

Not specific to Oracle, but you would use something of the form;

update table1
set table1.field2 = "New Value",
table1.field3 = table2.field3
from table1, table2
where table1.joinfield = table2.joinfield

Greg.

 

[grega]

Also .. I don't think you can update 2 tables in 1 update statement. I've never seen it done anyway :-(

Greg.

 

[saw15]

Just tried it Greg and rec'ed the following error:

Ora-00933: SQL command not properly ended.

Cursor then sits on the from portion of the statement.

Any thoughts?

 

[Swany]

update table1
set field1 = 'New Value',
field2 = (select field3
from table2
where table1.joinfield = table2.joinfield) .
.. Eat, think and be merry .
... ....................... .

 

[gizmo2]

Hi,

I'm not familiar with the finer points of oracle but I am getting the following error :

ORA-00933: SQL command not properly ended

in the following sql statement:

select contsupp.*,contsupp_1.contsupref from contsupp left join contsupp as contsupp_1 on contsupp.recid = contsupp_1.linkacct where contsupp.accountno='99101853375(L$#% Ber' and contsupp.rectype='C'

It works fine with DBASE IV.

Any help would be much appreciated.

Regards
Gizmo

 

[angiole]

I have never used this feature, but I have read some documentation in regards using views for table update.
I'm sure it ain't pretty!

 

[Swany]

Try this instead. Oracle doesn't support LEFT OUTER JOIN syntax. Oracles outer joins use a (+) operator in the where clause.

select contsupp.*,
contsupp_1.contsupref
from contsupp,
contsupp_1
where contsupp.recid = contsupp_1.linkacct (+)
and contsupp.accountno='99101853375(L$#% Ber'
and contsupp.rectype='C'
.
.. Eat, think and be merry .
... ....................... .