Update in Oracle

[dbinfoweb]

Hello, I have 2 tables and I want to update the 2nd table with a column called "flag" with 1 if there is a match between the two tables.

This is what I have so far...

update table2 set FLAG = 1 where...

I joined the tables with the primary key. But it did not work.

Thanks for any help in advance.

 

[vijaychin]

update table1 a
set a.flag = ( select b.flag
from table2 b
where b.primarykey=a.primarykey)

 

[HenryMonster]

Let me put my 2c:

in the vijaychin's solution there is one caveat:
if there is no record in table2 matching a.primarykey,
then the select query will return NULL as a value and this
value will populate the unmatched record of table1.

If this is what dbinfoweb wants, then it's fine,
but if not, then we have do add a where clause
to the update statement as follows:


update table1 a
set a.flag = ( select b.flag
from table2 b
where b.primarykey=a.primarykey)
where exists (select 'x'
from table2 b
where b.primarykey=a.primarykey)


Good luck!