Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE groups AS 
SELECT * FROM (
    VALUES 
    ('A', 'DUPLICATED NAME', 'X'),
    ('B', 'DUPLICATED NAME', 'X'),
    ('C', 'string', 'Y'),
    ('D', 'string', 'Z')
) as a(id, name, foreign_key);

SELECT * FROM groups;

CREATE TABLE items AS 
SELECT * FROM (
    VALUES
    ('value1', 'A'),
    ('value2', 'B'),
    ('value3', 'C'),
    ('value4', 'D')
) as a(id, group_id);

SELECT * FROM items
4 rows affected



id
name
foreign_key


A
DUPLICATED NAME
X

B
DUPLICATED NAME
X

C
string
Y

D
string
Z


4 rows affected



id
group_id


value1
A

value2
B

value3
C

value4
D


SELECT
    *,
    first_value(id) OVER (PARTITION BY name, foreign_key ORDER BY id)
FROM groups


id
name
foreign_key
first_value


A
DUPLICATED NAME
X
A

B
DUPLICATED NAME
X
A

C
string
Y
C

D
string
Z
D


SELECT
    i.*,
    g.id as g_id,
    g.first_value
FROM items i
JOIN (
    SELECT
        *,
        first_value(id) OVER (PARTITION BY name, foreign_key ORDER BY id)
    FROM groups
) g ON g.id = i.group_id 


id
group_id
g_id
first_value


value1
A
A
A

value2
B
B
A

value3
C
C
C

value4
D
D
D


WITH new_group_id AS (
    SELECT
        i.*,
        g.id as g_id,
        g.first_value
    FROM items i
    JOIN (
        SELECT
            *,
            first_value(id) OVER (PARTITION BY name, foreign_key ORDER BY id)
        FROM groups
   ) g ON g.id = i.group_id
), update_items AS (
   UPDATE items i
   SET group_id = s.first_value
   FROM (SELECT * FROM new_group_id) s
   WHERE s.id = i.id
)
DELETE FROM groups
WHERE id IN (
    SELECT g_id
    FROM new_group_id
    WHERE g_id <> first_value
);

SELECT * FROM groups;
SELECT * FROM items;
1 rows affected



id
name
foreign_key


A
DUPLICATED NAME
X

C
string
Y

D
string
Z




id
group_id


value1
A

value2
A

value3
C

value4
D