By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `sum_date_diff` (
`id_` int DEFAULT NULL,
`date_time` text,
`p1_id` int DEFAULT NULL,
`p2_id` int DEFAULT NULL,
`sum_date_diff` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `sum_date_diff` VALUES (1,'2000-01-01',1,2,NULL),(2,'2000-01-02',2,4,NULL),(3,'2000-01-04',1,3,NULL),(4,'2000-01-07',2,5,NULL),(5,'2000-01-15',2,3,NULL),(6,'2000-01-20',1,3,NULL),(7,'2000-01-31',1,3,NULL);
SELECT * FROM sum_date_diff
| id_ | date_time | p1_id | p2_id | sum_date_diff |
|---|---|---|---|---|
| 1 | 2000-01-01 | 1 | 2 | null |
| 2 | 2000-01-02 | 2 | 4 | null |
| 3 | 2000-01-04 | 1 | 3 | null |
| 4 | 2000-01-07 | 2 | 5 | null |
| 5 | 2000-01-15 | 2 | 3 | null |
| 6 | 2000-01-20 | 1 | 3 | null |
| 7 | 2000-01-31 | 1 | 3 | null |
UPDATE sum_date_diff AS sdd0
JOIN
(SELECT
id_,
SUM(DATEDIFF(sdd1.date_time, sq.date_time)) AS sum_date_diff
FROM
sum_date_diff AS sdd1
LEFT OUTER JOIN (SELECT
sdd2.date_time AS date_time, sdd2.p1_id AS player_id
FROM
sum_date_diff AS sdd2 UNION ALL SELECT
sdd3.date_time AS date_time, sdd3.p2_id AS player_id
FROM
sum_date_diff AS sdd3) AS sq ON sq.date_time < sdd1.date_time
AND sq.player_id = sdd1.p1_id
GROUP BY sdd1.id_) AS master_sq ON master_sq.id_ = sdd0.id_
SET
sdd0.sum_date_diff = master_sq.sum_date_diff
SELECT * FROM sum_date_diff
| id_ | date_time | p1_id | p2_id | sum_date_diff |
|---|---|---|---|---|
| 1 | 2000-01-01 | 1 | 2 | null |
| 2 | 2000-01-02 | 2 | 4 | 1 |
| 3 | 2000-01-04 | 1 | 3 | 3 |
| 4 | 2000-01-07 | 2 | 5 | 11 |
| 5 | 2000-01-15 | 2 | 3 | 35 |
| 6 | 2000-01-20 | 1 | 3 | 35 |
| 7 | 2000-01-31 | 1 | 3 | 68 |