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

517763 次浏览
小开
最佳答案 
update QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
set q.QuestionID = a.QuestionID
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.

小开 
UPDATE
"QuestionTrackings"
SET
"QuestionID" = (SELECT "QuestionID" FROM "Answers" WHERE "AnswerID"="QuestionTrackings"."AnswerID")
WHERE
"QuestionID" is NULL
AND ...
小开

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 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;
小开

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.

小开

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`;
小开

below works for mysql

update table1 INNER JOIN table2 on table1.col1 =  table2.col1
set table1.col1 =  table2.col2
小开

For Mysql You can use this Query

UPDATE table1 a, table2 b SET a.coloumn = b.coloumn WHERE a.id= b.id

小开

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;
小开 
UPDATE courses
INNER JOIN states on courses.state_id = states.id
SET courses.state_code = states.code
WHERE some_random_condition

Here we are updating courses table column course_code by referring to the master record states

小开

In case of Postgres you have to use the following structure:

UPDATE table1
SET colX = table2.colY
FROM table2
WHERE table1.id = table2.id;

提交答案