SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?

Question

update q
set q.QuestionID = a.QuestionID
from QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
where q.QuestionID is null -- and other conditions you might want

I recommend to check what the result set to update is before running the update (same query, just with a select):

select *
from QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
where q.QuestionID is null -- and other conditions you might want

Particularly whether each answer id has definitely only 1 associated question id.

Without the update-and-join notation (not all DBMS support that), use:

UPDATE QuestionTrackings
   SET QuestionID = (SELECT QuestionID
                        FROM AnswerTrackings
                        WHERE AnswerTrackings.AnswerID = QuestionTrackings.AnswerID)
   WHERE QuestionID IS NULL
     AND EXISTS(SELECT QuestionID
                        FROM AnswerTrackings
                        WHERE AnswerTrackings.AnswerID = QuestionTrackings.AnswerID)

Often in a query like this, you need to qualify the WHERE clause with an EXISTS clause that contains the sub-query. This prevents the UPDATE from trampling over rows where there is no match (usually nulling all the values). In this case, since a missing question ID would change the NULL to NULL, it arguably doesn't matter.

I don't know if you've run into the same problem than me on MySQL Workbench but running the query with the INNER JOIN after the FROM statement didn't work for me. I was unable to run the query because the program complained about the FROM statement.

So in order to make the query work I changed it to

UPDATE table1 INNER JOIN table2 on table1.column1 = table2.column1
SET table1.column2 = table2.column4
WHERE table1.column3 = 'randomCondition';

instead of

UPDATE a
FROM table1 a INNER JOIN table2 b on a.column1 = b.column1
SET a.column2 = b.column4
WHERE a.column3 = 'randomCondition';

I guess my solution is the right syntax for MySQL.

UPDATE
    "QuestionTrackings"
SET
    "QuestionID" = (SELECT "QuestionID" FROM "Answers" WHERE "AnswerID"="QuestionTrackings"."AnswerID")
WHERE
    "QuestionID" is NULL
AND ...