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?

  • What is plays_in_events? It is nowhere to be seen in your query but in the last line – cha Oct 17 '14 at 0:02
  • I thought it was a alias issue, but yeah I think it's typo. You have events table twice; one should be plays_in_events :P – Manny Calavera Oct 17 '14 at 0:04
  • The tables are joined events <--plays_in_events-->plays. The plays in events table has link fields EventID and PlayID. – JohnWMcC Oct 17 '14 at 0:06
  • @cha asked "What is plays_in_events" -- it's the table making the many-to-many relationship between events and playsand appears twice in the INNER JOINS as well as in the last line. – JohnWMcC Oct 17 '14 at 0:12
  • @cha and I'm saying you have typographical error in query statement. You don't have plays_in_events table in your joins. Instead, you wrote events table twice. – Manny Calavera Oct 17 '14 at 0:28

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_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

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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