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?
SET QuestionID = (need some select query that will get the QuestionID from the AnswerID in this row)
WHERE QuestionID is NULL AND ... ?
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.