Oracle update one table based on value in another - Can't get this right.

[cjax920]

Business case:

Table1 did not get updated by someone elses data load, so I have to fix it. (Yay!)

Anyway, here is the case.

Table1.hrc needs updated to match Table2.hrc with the following conditions:
Table1.num = Table2.num
and Table2.status = 'HOLD'
and Table1.hrc is null
and Table2.hrc is not null

I know this should be easy, but I can't get it to return a single row(always multiple). The following query returns the records I need to update, I just can't get the update to run as this selects multiple records.

select Table2.num, Table1.num, Table2.hrc, Table1.hrc, Table2.status
from Table2 w, Table1 s
where Table2.hrc is not null
and Table1.hrc is null
and Table2.status = 'HOLD'
and Table2.num = Table1.num
and Table2.siteid = Table1.siteid
group by Table2.num, Table1.num, Table2.hrc, Table1.hrc, Table2.status;

Thanks in advance.

 

[taupirho]

First things first. You're not doing any aggregation so your group by clause is redundant. Secondly you don't use the correalation names w and s anywhere so ditch those also. Next thing is, see if you can use updateable join view syntax. You might not be able to.

update(
select Table2.hrc oldhrc, Table1.hrc newhrc
from Table2 , Table1
where Table2.hrc is not null
and Table1.hrc is null
and Table2.status = 'HOLD'
and Table2.num = Table1.num
and Table2.siteid = Table1.siteid
)
set newhrc = oldhrc

If you get an non-key preserveed error when running the above you can't do it this way. No matter. It's a bit difficult to second guess update statements when you dont know whats in the tables and it would be helpful if you could provide some test data as well as table creation/insert statements. But in the meantime try this

update table1 t1
set t1.hrc = (select hrc
from table2 t2
where t1.hrc is null
and t2.siteid = t1.siteid
and t2.num = t1.num
and t2.status = 'HOLD')
and exists (select 'x' from table2
where t1.hrc is null
and siteid = t1.siteid
and num = t1.num
and status = 'HOLD')



In order to understand recursion, you must first understand recursion.