0

I have a table of team players in which the PlayerID is missing in some rows.

CREATE TABLE TeamPlayers
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
TeamID int(11) unsigned NOT NULL,
PlayerID int(11) unsigned,
Name varchar(255),
INDEX(PlayerID),
INDEX(Name),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

I try to match the PlayerID for each name. However, as names are not unique (two persons can have the same name), I must rely on some approximations.

The first try is to assume the names of co-players is unique. For example,

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        NULL        John West
3     16        NULL        Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

Assuming Team Cook played with only one person named John West and vice versa.

Expected Output:

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        5           John West
3     16        9           Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

I started with

SELECT a.Name,MAX(a.PlayerID),MAX(b.PlayerID),
    GROUP_CONCAT(a.ID SEPARATOR ','),    GROUP_CONCAT(b.ID SEPARATOR ',') 
  FROM TeamPlayers a JOIN TeamPlayers b 
  ON a.TeamID=b.TeamID AND a.Name<>b.Name
  GROUP BY a.Name

but what is the right way to UPDATE the table?

SQL Fiddle

6
  • 1) Expand your sample data - it must include all possible combinations (the same players, different players with the same name). 2) Show desired output for this sample data with detailed explanations for each separate updated row. 3) Does MariaDB 10.5 is real version of your DB server? 4) what is the right way to UPDATE the table? See RM, UPDATE Syntax, multiple-table syntax.
    – Akina
    Commented Dec 29, 2021 at 4:37
  • @Akina 1) Added more data, 2) added the expected output, 3) yes I use MariadB 10.5. 4) by the right way, I did not mean UPDATE of multiple-table. I meant this specific case, in which we use GROUP BY to UPDATE both ways, without messing up.
    – Googlebot
    Commented Dec 29, 2021 at 5:18
  • Does the team always consist of 2 players?
    – Akina
    Commented Dec 29, 2021 at 5:35
  • @Akina no, any number of players.
    – Googlebot
    Commented Dec 29, 2021 at 5:40
  • And we must copy palyer ID only when the team players set is the same? or we'll lookung on the players pairs only?
    – Akina
    Commented Dec 29, 2021 at 5:43

1 Answer 1

1

Test this:

UPDATE TeamPlayers t1
JOIN ( SELECT LEAST(t1.Name, t2.Name) Name1, 
              MAX(CASE WHEN LEAST(t1.Name, t2.Name) = t1.Name
                       THEN t1.PlayerID
                       ELSE t2.PlayerID
                       END) OVER () ID1,
              GREATEST(t1.Name, t2.Name) Name2, 
              MAX(CASE WHEN GREATEST(t1.Name, t2.Name) = t1.Name
                       THEN t1.PlayerID
                       ELSE t2.PlayerID
                       END) OVER () ID2,
              TeamID
      FROM TeamPlayers t1
      JOIN TeamPlayers t2 USING (TeamID)
      WHERE t1.Name <> t2.Name
      GROUP BY Name1, Name2, TeamID ) t2 USING (TeamID)
SET t1.PlayerID = CASE WHEN t1.Name = t2.Name1
                       THEN t2.ID1
                       ELSE t2.ID2
                       END;

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=16f5b70672ce37a20ce120f29012a646

PS. Maybe the window definition needs in correct PARTITION BY (PARTITION BY Name1, Name2, TeamID?) - but the sample data array is too tiny and does not contain enough data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.