How to Update a Column in One Table Using Values from a Related Table?

Update columns with values ​​from related tables

This article explores how to update a column in a table (QuestionTrackings) whose values ​​come from a related table (QuestionAnswers) based on a specific condition (QuestionID is NULL). We can achieve this using UPDATE statement combined with INNER JOIN.

SQL query:

<code class="language-sql">UPDATE QuestionTrackings q
INNER JOIN QuestionAnswers a
ON q.AnswerID = a.AnswerID
SET q.QuestionID = a.QuestionID
WHERE q.QuestionID IS NULL
-- 可在此处添加其他条件</code>

Description:

• Use INNER JOIN to establish a connection between two tables based on the common column AnswerID.
• Condition q.QuestionID IS NULL ensures that the update only affects rows in the QuestionTrackings table where QuestionID is NULL.
The
• SET clause updates the QuestionID column in the QuestionTrackings table (q.QuestionID) with the corresponding QuestionID from the QuestionAnswers table (a.QuestionID).

Note:

Before executing an UPDATE query, it is recommended to preview the affected data. To do this, you can modify your query to include the following:

<code class="language-sql">SELECT *
FROM QuestionTrackings q
INNER JOIN QuestionAnswers a
ON q.AnswerID = a.AnswerID
WHERE q.QuestionID IS NULL
-- 可在此处添加其他条件</code>

This will display the rows that will be updated and confirm that each AnswerID has only one associated QuestionID to prevent data inconsistencies.

The above is the detailed content of How to Update a Column in One Table Using Values from a Related Table?. For more information, please follow other related articles on the PHP Chinese website!