Pfthb

I want to clean up my table:

rid(PK) rval
----------- ----------
10 test
11 Rtest
12 dummy
13 test
14 Rtest

to remove the duplicate values from column rval, so I end up with:

rid(PK) rval
----------- ----------
10 test
11 Rtest
12 dummy

But I have another table with a foreign key relationship which uses the values I want to delete:

ruid rid(FK)
---------- ----------
1 10
2 10
3 12
4 13
5 13
6 14
7 14

so before I delete, I need to update that table to use the matching rid that I'm keeping, e.g. updating 14 to 11; so that table will end up as:

ruid rid(FK)
---------- ----------
1 10
2 10
3 12
4 10
5 10
6 11
7 11

How can I do that?

You could use an analytic aggregate to get the lowest rid for each rval:

min(rid) over (partition by rval)

and use that in a subquery that joins the two tables (which I've imaginatively called t1 and t2 as you haven't given their real names) so you can see the lowest suitable rid for each ruid:

select t2.ruid, t2.rid, x.min_rid
from (
 select rid, min(rid) over (partition by rval) as min_rid from t1
) x
join t2 on t2.rid = x.rid;

 RUID RID MIN_RID
---------- ---------- ----------
 1 10 10
 2 10 10
 3 12 12
 4 13 10
 5 13 10
 6 14 11
 7 14 11

You can then use that in a merge statement:

merge into t2
using (
 select t2.ruid, t2.rid, x.min_rid
 from (
 select rid, min(rid) over (partition by rval) as min_rid from t1
 ) x
 join t2 on t2.rid = x.rid
) y
on (t2.ruid = y.ruid)
when matched then update set t2.rid = y.min_rid
where t2.rid != y.min_rid;

4 rows merged.

select * from t2;

 RUID RID
---------- ----------
 1 10
 2 10
 3 12
 4 10
 5 10
 6 11
 7 11

Once you've done that you can remove the redundant PK values:

delete from t1
where exists (select * from t1 x where x.rval = t1.rval and x.rid < t1.rid);

2 rows deleted.

select * from t1;

 RID RVAL 
---------- -----
 10 test 
 11 Rtest
 12 dummy

Id ruid is not unique, and there is no other unique key you can include in the statement, then you can't really use a merge - you can't refer to rid in the on clause as that's the column you're updating.

You could do a correlated update instead:

update t2
set rid = (
 select min_rid
 from (
 select rid, min(rid) over (partition by rval) as min_rid from t1
 ) x
 where x.rid = t2.rid
);

That will update every row; if you have a lot of data and/or a small proportion need to change then you can add a filter to only update those:

update t2
set rid = (
 select min_rid
 from (
 select rid, min(rid) over (partition by rval) as min_rid from t1
 ) x
 where x.rid = t2.rid
)
where rid != (
 select min_rid
 from (
 select rid, min(rid) over (partition by rval) as min_rid from t1
 ) x
 where x.rid = t2.rid
);