SQL UPDATE设置了一个列，以等于在另一个列引用的相关表中的值?

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.

问题和答案(每个都有一个表)。因此，由于有一个bug，有一堆问题被设置为NULL，但是一个相关的答案的问题在答案表中。

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.

所以说，问号是空的，答案是500，如果我们找到答案表，找到答案500，有一个列有问题id，它应该是空值所在的位置。

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

因此，基本上我要设置每个空问号，以等于在trackings表中答案行中找到的答案行中找到的问题id(与正在编写的空问题id相同的行)。

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

不知道如何才能让它将问题id分配给匹配的答案。

7 个解决方案

#1

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.

特别是每个答案id是否只有一个关联的问题id。

#2

Without the update-and-join notation (not all DBMS support that), use:

如果没有update-and-join表示法(不是所有的DBMS支持)，请使用:

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.

通常在这样的查询中，您需要使用包含子查询的EXISTS子句来限定WHERE子句。这样可以防止在没有匹配的情况下(通常为所有的值都是无效的)对行进行践踏。在本例中，由于缺失的问题ID会将NULL更改为NULL，因此可以认为这并不重要。

#3

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

#4

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.

我不知道在MySQL工作台中是否遇到了与我相同的问题，但是在从语句中运行查询并没有为我工作。我无法运行查询，因为程序抱怨来自语句。

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.

我想我的解决方案是MySQL的正确语法。

#5

I was having the same question. Here is a working solution which is similar to eglasius's. I am using postgresql.

我也有同样的问题。这是一个类似于eglasius的工作解决方案。我使用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.

如果在第1行中使用的是表名，它会抱怨，在第2行中没有任何问题。

#6

 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;

#7

Update 2nd table data in 1st table need to Inner join before SET :

在第1表中更新第2表数据，需要在设置前内连接:

`UPDATE `table1` INNER JOIN `table2` ON `table2`.`id`=`table1`.`id` SET `table1`.`name`=`table2`.`name`, `table1`.`template`=`table2`.`template`;