MySQL Update a column depending on multi tables

Question

Hi all,

I have a question regarding updating a MySQL database.

I have three tables: Match, Submission and SubmissionVersion. A SubmissionVersion can be set as 'Favorite'. But I can't just query UPDATE SubmissionVersion SET IsFavorite = 1 WHERE ID = $ID because of the relation to a Submission and than the Match. My question is how can I update a SubmissionVersion column with a MySQL Query with two joins? I've tried this query but I can't get it to work.

UPDATE
    SubmissionVersion
JOIN
    Submission
ON
    Submission.ID, SubmissionVersion.SubmissionID
JOIN
    Match
ON
    Match.ID ON Submission.MatchID
SET
    SubmissionVersion.IsFavorite    = ".$Index."
WHERE
    SubmissionVersion.ID        = ".$ID."
AND
    Match.ID            = ".$MatchID

Kind regards from Holland, Thanks for the effort you take to write back to me!

Ben Fransen

Answer 1

UPDATE SubmissionVersion sv
SET    sv.IsFavorite = ".$Index."
WHERE  sv.ID = ".$ID."
AND    sv.ID IN (
       SELECT s.ID
       FROM   Submission s
       WHERE  s.MatchID  = ".$MatchID'")

If I understand your statement correctly, that should work.

I eliminated the Match table completely since you're just checking the value against a column in Submission.

Answer 2

Let's start with saying that MATCH is MySQL's reserved word, so needs to be put into backticks.

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html