I have two tables, events
and plays
joined many-to-many by a third table plays_in_events
.
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.
LATER
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 lineplays_in_events
" -- it's the table making the many-to-many relationship betweenevents
andplays
and appears twice in the INNER JOINS as well as in the last line.plays_in_events
table in your joins. Instead, you wroteevents
table twice.