Craig Harley Craig Harley - 2 months ago 5
MySQL Question

Opening Same Table Twice In Update

I'm trying to normalise a large dataset, I've built a table with all the relationships, called App(earances). Then I loop through another table to build a temp table which contains the duplicates, with MasterID being the one I want to keep.

The data in the duplicate table looks like this:
enter image description here

I then try to update the app table, swapping any duplicate id's for the corresponding master id, but I'm getting the Error: Can't reopen table: 'd'.

Here is the code:

DROP TABLE IF EXISTS Duplicates;
CREATE TEMPORARY TABLE Duplicates (
MasterID int NOT NULL,
DuplicateID int NOT NULL
);
INSERT INTO Duplicates(MasterID, DuplicateID)
SELECT p1.PlayerID as MasterID, p2.PlayerID as DuplicateID
FROM Player p1
LEFT JOIN Player p2 on p1.Name = p2.Name
WHERE p1.name = p2.name
AND p1.PlayerID < p2.PlayerID
ORDER BY p1.PlayerID;

UPDATE app a
SET a.PlayerID = ( SELECT d.MasterID FROM Duplicates d WHERE a.PlayerID = d.DuplicateID LIMIT 1 )
WHERE a.PlayerID in (SELECT d.DuplicateID FROM Duplicates d);

DELETE Player p
WHERE PlayerID = ( SELECT d.DuplicateID FROM Duplicates d )
DROP TABLE Duplicates;


The problem is with the update query, I've put the other queries in so you can get a better idea of what's going on, I think a CTE would be better here but I don't know how I could do it. I'm running this in MYSQL at the moment but I could use another SQL variant.

Thanks for the help

Answer

One method uses join:

UPDATE app a JOIN
       Duplicates d
       ON a.PlayerID = d.DuplicateID       
    SET a.PlayerID =  d.MasterID;

a will get set from an arbitrary row in d, if there are multiple matches in d for a given a.

I suppose it is not a great idea to have multiple possible rows update a single row, so you could aggregate before the join:

UPDATE app a JOIN
       (SELECT d.DuplicateID, MAX(d.MasterId) as MasterId
        FROM Duplicates d
        GROUP BY d.DuplicateID
       ) d
       ON a.PlayerID = d.DuplicateID       
    SET a.PlayerID =  d.MasterID;
Comments