I have two tables,
plays joined many-to-many by a third table
Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in that linked event.
I have tried
UPDATE `events` INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`) INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`) SET events.Name = plays.Play WHERE events.Name IS NULL and plays_in_events.PlayID = 1
but get a syntax error
SQL Error: Not unique table/alias: 'events'
(I'm trying to limit any damage from accidental multiple updates by only testing it in the first instance for PlayID = 1, but have hundreds of records to update, so I don't want to do it manually).
There is only one table
events in the database.
What's going wrong here please? I can't see it, unfortunately.
Maybe I've got the JOIN wrong altogether.
This SELECT query works to join the two main tables, and was produced by a visual query editor:
SELECT `events`.`EventID`, `events`.`Name`, `plays`.`Play`, `plays`.`PlayID` FROM `plays_in_events` INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`) INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`) WHERE `events`.`Name` IS NULL AND `plays`.`PlayID` < 10
How could I make the JOINS in the UPDATE based on this working?
plays_in_events? It is nowhere to be seen in your query but in the last line
plays_in_events" -- it's the table making the many-to-many relationship between
playsand appears twice in the INNER JOINS as well as in the last line.
plays_in_eventstable in your joins. Instead, you wrote