将值从一行复制到共享另一个相同值的另一行

Question

我有一个类似的表

合同A127B114021-01-01C227020-05-01//tr>D214<2021-01-01>

Answer 1

如果14和27将是静态数据，则尝试以下操作：

update f1
set f1.beginn = f2.beginn
from MyTable f1
inner join MyTable f2 on f1.ID != f1.ID and f1.contract = f2.contract and f1.role = 14 and f2.role = 27

Answer 2

merge怎么样？

之前：

SQL> select * from test order by id;
I   CONTRACT       ROLE BEGINN
- ---------- ---------- ----------
A          1         27 01.01.2020
B          1         14 01.01.2021
C          2         27 01.05.2020
D          2         14 01.01.2021

合并：

SQL> merge into test a
2    using (select contract, beginn
3           from test b
4           where b.role = 27
5          ) x
6    on (a.contract = x.contract)
7  when matched then update set
8    a.beginn = x.beginn
9    where a.role = 14;
2 rows merged.

之后：

SQL> select * from test order by id;
I   CONTRACT       ROLE BEGINN
- ---------- ---------- ----------
A          1         27 01.01.2020
B          1         14 01.01.2020
C          2         27 01.05.2020
D          2         14 01.05.2020
SQL>