Hi,

I have two tables in a database to work with, and I want to use both but update a single field in only one of the DBs.

I have a Select:

select a.code
a.desc
b.keyval
from table_1 a,
table_2 b
where a.desc = 'TEST'
and left(b.keyval, length(a.code)) = a.code
and b.classval = 'R'

And say I wanted to update another field on table_2, on all records that would be pulled from the above Select.

So the basic update statement would be:

update table_2
set foo = 'hello'

How can I join the two?

Hope I've explained that clearly... Any help would be appreciated, thanks.

Can u be little more explainatory ? Give the table structures and the what u want to do in detail so that everyone could have a clear understanding

Indeed we are missing some information here, but your statement will probably end up like the following :

UPDATE TABLE_2 upd
SET upd.FOO = (SELECT one_column_or_expression FROM TABLE_1 t1, TABLE_2 t2 WHERE <where_clause_subselect>)
WHERE <where_clause_upd>

(upd = alias for table_2, the one that needs to be updated

The questions you should ask yourself are :
1. What are the rows I want to update in TABLE_2. If you want them all to be updated, then you can omit the where clause <where_clause_upd>. Otherwise, you will have to restrict the number of rows with this where clause.

2. Now that Oracle knows what rows in TABLE_2 need to be updated, you will have to tell it what value you want to assign to column FOO. That's where the subselect comes in. The subselect will be evaluated for each row matching <where_clause_upd>. Your subselect needs to return 1 row at the most. If your subselect returns more than 1 row, Oracle will raise an error. If your subselect returns 0 rows, a null value will be assigned to FOO. In the <where_clause_subselect>, you can refer to 3 "sources" of information : t1 (table_1) t2 (table_2 in select) and upd (table_2 that will be updated).

Just give it a try...