Re: How to update column in another database

Ron McBay wrote:
>
> I want to update the values in a column in a table in another database based
> on the values in a column in a table in a local database. Is this possible?
> I wanted to use a command like:
>
> UPDATE table1_at_db1 a, table2 b
> SET a.column1 = b.column2
> WHERE a.col_id = b.col_id
>
> That didn't seem to work and I don't know whether I'm just using the wrong
> syntax or whether what I want to do just isn't possible.
>
> Please advise.

You can update a table in a non-local database. However, Oracle does not allow multiple tables specify after the Update keyword. See Oracle's documentation: SQL Language Reference Manual on Update examples. You need to do the following instead:

UPDATE table1_at_db1 a
SET a.column1 =

	(select b.column2
	 from table2 b
	 where a.col_id = b.col_id);

However, if there are col_id in table1 that are not in table2, you need to specify the where clause in the update statement:

UPDATE table1_at_db1 a
SET a.column1 =

	(select b.column2
	 from table2 b
	 where a.col_id = b.col_id)
Where exits
	(select b.column2
	 from table2 b
	 where a.col_id = b.col_id)

Lorinda Received on Wed Dec 04 1996 - 00:00:00 CET