Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE person (
  pid int
, name text
, dob date
, younger_sibling_name text
, younger_sibling_dob date
);

-- proper test case with successive twins
INSERT INTO person (pid, name, dob) VALUES 
  (1, 'John2' , '1980-01-05')  -- twins 1
, (2, 'Jimmy' , '1975-04-25')
, (3, 'Sarah' , '2004-02-10')  -- twins 2
, (4, 'Frank' , '1934-12-12')
, (5, 'John'  , '1980-01-05')  -- twins 1
, (6, 'Sarah2', '2004-02-10')  -- twins 2
, (7, 'Xaver' , '2005-02-10')
, (8, 'Zulu'  , '2006-02-10')
;

CREATE TABLE



INSERT 0 8


-- NEW variant (recommended!)
BEGIN;

UPDATE person p
SET    younger_sibling_name = y.name
     , younger_sibling_dob  = y.dob
FROM  (
   SELECT dob, name, lead(dob) OVER (ORDER BY dob) AS next_dob
   FROM  (
      SELECT DISTINCT ON (dob)
             dob, name
      FROM   person p
      ORDER  BY dob, name  -- or any other additional ORDER BY items to break ties
      ) sub
   ) y
WHERE  p.dob = y.next_dob;

SELECT * FROM person ORDER BY dob;

ROLLBACK;  -- to reuse test case in following tests

BEGIN



UPDATE 7




pid
name
dob
younger_sibling_name
younger_sibling_dob


4
Frank
1934-12-12
null
null

2
Jimmy
1975-04-25
Frank
1934-12-12

5
John
1980-01-05
Jimmy
1975-04-25

1
John2
1980-01-05
Jimmy
1975-04-25

3
Sarah
2004-02-10
John
1980-01-05

6
Sarah2
2004-02-10
John
1980-01-05

7
Xaver
2005-02-10
Sarah
2004-02-10

8
Zulu
2006-02-10
Xaver
2005-02-10



SELECT 8



ROLLBACK


-- OLD variant 1
BEGIN;

WITH cte AS (
   SELECT dob, min(name) AS name
        , row_number() OVER (ORDER BY dob) rn
   FROM   person p
   GROUP  BY dob
   )
UPDATE person p
SET    younger_sibling_name = y.name
     , younger_sibling_dob  = y.dob
FROM   cte x
JOIN   cte y ON y.rn = x.rn - 1
WHERE  p.dob = x.dob;

SELECT * FROM person ORDER BY dob;

ROLLBACK;

BEGIN



UPDATE 7




pid
name
dob
younger_sibling_name
younger_sibling_dob


4
Frank
1934-12-12
null
null

2
Jimmy
1975-04-25
Frank
1934-12-12

5
John
1980-01-05
Jimmy
1975-04-25

1
John2
1980-01-05
Jimmy
1975-04-25

3
Sarah
2004-02-10
John
1980-01-05

6
Sarah2
2004-02-10
John
1980-01-05

7
Xaver
2005-02-10
Sarah
2004-02-10

8
Zulu
2006-02-10
Xaver
2005-02-10



SELECT 8



ROLLBACK


-- OLD variant 3
WITH cte AS (
   SELECT *, dense_rank() OVER (ORDER BY dob) AS drk
   FROM   person
   )
UPDATE person p
SET    younger_sibling_name = y.name
     , younger_sibling_dob  = y.dob
FROM   cte x
JOIN   (SELECT DISTINCT ON (drk) * FROM cte) y ON y.drk = x.drk - 1
WHERE  x.pid = p.pid;

SELECT * FROM person ORDER BY dob;

UPDATE 7




pid
name
dob
younger_sibling_name
younger_sibling_dob


4
Frank
1934-12-12
null
null

2
Jimmy
1975-04-25
Frank
1934-12-12

5
John
1980-01-05
Jimmy
1975-04-25

1
John2
1980-01-05
Jimmy
1975-04-25

3
Sarah
2004-02-10
John2
1980-01-05

6
Sarah2
2004-02-10
John2
1980-01-05

7
Xaver
2005-02-10
Sarah2
2004-02-10

8
Zulu
2006-02-10
Xaver
2005-02-10



SELECT 8

pid	name	dob	younger_sibling_name	younger_sibling_dob
4	Frank	1934-12-12	null	null
2	Jimmy	1975-04-25	Frank	1934-12-12
5	John	1980-01-05	Jimmy	1975-04-25
1	John2	1980-01-05	Jimmy	1975-04-25
3	Sarah	2004-02-10	John	1980-01-05
6	Sarah2	2004-02-10	John	1980-01-05
7	Xaver	2005-02-10	Sarah	2004-02-10
8	Zulu	2006-02-10	Xaver	2005-02-10