Advertisement
Advertisement



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

2009/04/01
1
113
4/1/2009 9:19:04 PM

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

2009/04/01
171
4/1/2009 9:24:23 PM

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.

2015/09/10

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.

2015/09/10

UPDATE
    "QuestionTrackings"
SET
    "QuestionID" = (SELECT "QuestionID" FROM "Answers" WHERE "AnswerID"="QuestionTrackings"."AnswerID")
WHERE
    "QuestionID" is NULL
AND ...
2009/04/01

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.

2011/12/01

 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;
2015/03/01

Source: https://stackoverflow.com/questions/707371
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]