SQL UPDATE SET 一列等于不同列引用的相关表中的值？

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

提问by MetaGuru

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.

QuestionID 和 AnswerID（每个都有一个表格）。因此，由于错误，有一堆 QuestionID 设置为 NULL，但相关 AnswerID 的 QuestionID 位于 Answers 表中。

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.

因此，假设 QuestionID 为 NULL，AnswerID 为 500，如果我们转到 Answers 表并找到 AnswerID 500，那么有一列包含 QuestionID 的列应该是 NULL 值所在的位置。

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

所以基本上我想将每个 NULL QuestionID 设置为等于在跟踪表中的 AnswerID 的 Answers 行的 Answers 表中找到的 QuestionID （与正在写入的 NULL QuestionID 相同的行）。

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

不知道我将如何使它从匹配的 AnswerID 中将 QuestionID 分配给 QuestionID ...

回答by eglasius

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 是否肯定只有 1 个关联的问题 id。

回答by Jonathan Leffler

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

如果没有更新和加入符号（并非所有 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 子句。这可以防止 UPDATE 践踏不匹配的行（通常将所有值归零）。在这种情况下，由于缺少问题 ID 会将 NULL 更改为 NULL，因此可以说这无关紧要。

回答by AxeEffect

I don't know if you've run into the same problem than me on MySQL Workbench but running the query with the INNER JOINafter the FROMstatement didn't work for me. I was unable to run the query because the program complained about the FROMstatement.

我不知道您是否在 MySQL Workbench 上遇到了与我相同的问题，但是使用INNER JOINafterFROM语句运行查询对我不起作用。我无法运行查询，因为程序抱怨该FROM语句。

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 的正确语法。

回答by Milen A. Radev

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

回答by Lighting Minds

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 行中使用 q 代替表名，它会抱怨，并且在第 2 行中的 QuestionID 之前不应有任何内容。

回答by Frank

 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;

回答by Samir Patel

For Mysql You can use this Query

对于 Mysql 你可以使用这个查询

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

更新 table1 a, table2 b SET a.coloumn = b.coloumn WHERE a.id= b.id

回答by Santosh Singh

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

更新 1st table 中的 2nd table 数据需要在 SET 之前进行内部联接：

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

回答by Pravin

below works for mysql

以下适用于 mysql

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

回答by Noob

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;