I have a table similar to

I would like to copy the beginn value of the 27 roles to the beginn of the 14 roles. This needs to be done grouped by the contract number.

Eg. Beginn of row Id A should be copied to beginn of row id B and Beginn of row Id C should be copied to beginn of row id D

Desired outcome:

Something like, but grouped by contract:

UPDATE MyTable
SET
    beginn = otherRoleData.beginn
FROM
    (
        SELECT
            beginn
        FROM
            mytable
        WHERE
            role = 27
    ) otherRoleData
WHERE
    role =14;

How would I do this? I struggle with the group by condition...

CodePudding user response：

If 14 and 27 will be statical data, then try this:

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

CodePudding user response：

How about merge?

Before:

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

Merge:

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.

After:

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>