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...
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.
Read more... Read less...
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
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';
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 ...
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.
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;