2

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?

8
  • What is plays_in_events? It is nowhere to be seen in your query but in the last line
    – cha
    Commented Oct 17, 2014 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 Commented Oct 17, 2014 at 0:04
  • The tables are joined events <--plays_in_events-->plays. The plays in events table has link fields EventID and PlayID.
    – JohnWMcC
    Commented Oct 17, 2014 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
    Commented Oct 17, 2014 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. Commented Oct 17, 2014 at 0:28

1 Answer 1

2

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

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.