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

Question

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

User · Answer

For Mysql You can use this Query  UPDATE table1 a  table2 b SET a coloumn   b coloumn WHERE a id  b id

User · Answer

below works for mysql  update table1 INNER JOIN table2 on table1 col1    table2 col1 set table1 col1    table2 col2

User · Answer

I was having the same question  Here is a working solution which is similar to eglasius s  I am using postgresql   UPDATE QuestionTrackings SET QuestionID   a QuestionID FROM QuestionTrackings q  QuestionAnswers a WHERE q QuestionID IS NULL   It complains if q was used in place of table name in line 1  and nothing should precede QuestionID in line 2

User · Answer

UPDATE      QuestionTrackings  SET      QuestionID     SELECT  QuestionID  FROM  Answers  WHERE  AnswerID   QuestionTrackings   AnswerID   WHERE      QuestionID  is NULL AND

User · Answer

I think this should work   UPDATE QuestionTrackings SET QuestionID    SELECT QuestionID                   FROM AnswerTrackings                   WHERE AnswerTrackings AnswerID   QuestionTrackings AnswerID  WHERE QuestionID IS NULL AND AnswerID IS NOT NULL

User · Answer

Update 2nd table data in 1st table need to Inner join before SET      UPDATE  table1  INNER JOIN  table2  ON  table2   id   table1   id  SET  table1   name   table2   name    table1   template   table2   template

User · Answer

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

User · Answer

select p post title m meta value sale price  n meta value   regular price     from  wp postmeta m      inner join wp postmeta n       on m post id    n post id     inner join wp posts p       ON m post id p id      and m meta key     sale price      and  n meta key     regular price       AND p post type    product       update  wp postmeta m  inner join wp postmeta n   on m post id    n post id inner join wp posts p   ON m post id p id  and m meta key     sale price  and  n meta key     regular price   AND p post type    product   set m meta value   n meta value

User · 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

User · Answer

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

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

Examples related to sql