使用其他表的值更新表

RGM 发表于 Dev

RGM

我想收拾桌子：

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

从column删除重复的值rval，所以我最终得到：

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

但是我还有另一个具有外键关系的表，该表使用我要删除的值：

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

所以在删除之前，我需要更新该表以使用rid我保持的匹配，例如将14更新为11；这样该表将最终显示为：

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

我怎样才能做到这一点？

亚历克斯·普尔

您可以使用分析聚合来获取rid每个的最低值rval：

min(rid) over (partition by rval)

并在连接两个表的子查询中使用该查询（我已经想象性地调用了该表，t1并且t2您没有提供其真实姓名），因此您可以看到适用rid于每个表的最低表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

然后可以在merge语句中使用它：

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

完成后，您可以删除冗余的PK值：

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

Idruid不是唯一的，并且您不能在语句中包含其他唯一键，那么您就不能真正使用合并-您不能rid在on子句中引用它，因为这就是您要更新的列。

您可以改为进行相关更新：

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
);

这将更新每一行；如果您有大量数据和/或需要更改的比例很小，则可以添加过滤器以仅更新那些数据：

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
);

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。