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
4 rows affected



id
name
foreign_key


A
DUPLICATED NAME
X

B
DUPLICATED NAME
X

C
string
Y

D
string
Z


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
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
    *
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
id
name
foreign_key
first_value


value1
A
A
DUPLICATED NAME
X
A

value2
B
B
DUPLICATED NAME
X
A

value3
C
C
string
Y
C

value4
D
D
string
Z
D


UPDATE items i
SET group_id = s.first_value
FROM (
    SELECT
        i.*,
        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
) s
WHERE i.id = s.id;

SELECT * FROM items
4 rows affected



id
group_id


value1
A

value2
A

value3
C

value4
D

id	group_id	id	name	foreign_key	first_value
value1	A	A	DUPLICATED NAME	X	A
value2	B	B	DUPLICATED NAME	X	A
value3	C	C	string	Y	C
value4	D	D	string	Z	D