ioSamurai ioSamurai - 2 months ago 9
SQL Question

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

I hope that made sense, let me elaborate:

There is a table of tracking data for a quiz program where each row has..

QuestionID and AnswerID (there is a table for each). So because of a bug there were a bunch of QuestionIDs set to NULL, but the QuestionID of a related AnswerID is in the Answers table.

So say QuestionID is NULL and AnswerID is 500, if we go to the Answers table and find AnswerID 500 there is a column with the QuestionID that should have been where the NULL value is.

So basically I want to set each NULL QuestionID to be equal to the QuestionID found in the Answers table on the Answer row of the AnswerID that is in the trackings table (same row as the NULL QuestionID that is being written).

How would I do this?

UPDATE QuestionTrackings
SET QuestionID = (need some select query that will get the QuestionID from the AnswerID in this row)
WHERE QuestionID is NULL AND ... ?


Not sure how I will be able to make it assign the QuestionID to the QuestionID from the matching AnswerID...

Answer
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.