从SELECT结果更新 [英] UPDATE from result of SELECT

问题描述

使用另一个表中的选择来更新我的表时遇到问题。这是我的描述：

I have a problem with updating my table with a select from another table. Here is my description:

表部分具有以下字段：

part_num PK
active 
notes
weight

表 importedDocument 具有以下字段：

part_num PK
active
notes
weight
quantity PK
condition_id  PK

part_num 在$code> part 中是唯一的，但 importedDocument 中的> part_num 不是。 importedDocument 中的每个 part_num 也在 part 中。我想做的是从 importedDocuemnt DISTINCT part_num >，并根据此结果，我想更新有效，笔记和重量部分用于 importedDocument 中的所有 part_num 。

part_num in part is unique, but part_num in importedDocument is not. Every part_num that is in importedDocument is also in part. What I want to do is to get DISTINCT part_num from importedDocuemnt, and with this result I want to update active, notes and weight in part for all the part_num that are in importedDocument.

到目前为止，我有这个：

So far I have this:

UPDATE part
   SET    
   active = importedDocument.active, 
   notes = importedDocument.notes,      
   weight = importedDocument.weight, 
   condition_id = importedDocument.condition_id
FROM  importedDocument
WHERE part.part_num IN (SELECT part_num from importedDocument);

我不明白为什么同样的钞票和为 importedDocument 中的所有零件设置了 condition_id 。

I dont understand why the same notes and condition_id is set for all parts from importedDocument.

推荐答案

您的问题是不完整。由于表 importedDocument 中的表 part 中的单个行可以有多个匹配项，因此您必须定义要选择的内容

Your question is incomplete. Since there can be multiple matches in table importedDocument for a single row in table part you have to define what to pick in such a case.

无论如何，您的解决方案都是完全错误的。您的条件是在 importedDocument 的 any 行中存在 part.part_num ，但是有各个行之间没有实际的连接，因此您最终得到了有效的 CROSS JOIN （笛卡尔积），部分中的每行都用每更新限定行 importedDocument （即多次！），（任意确定的）最后一次更新仍然有效。昂贵的废话，绝对不是您想要的。

Your solution is straight out wrong in any case. Your condition is that part.part_num exists in any row of importedDocument, but there is no actual connection between individual rows, so you end up with an effective CROSS JOIN (a Cartesian product), every row in part is updated with every qualifying row importedDocument (i e. many times!), the (arbitrarily determined) last update sticks. Expensive nonsense, definitely not what you want.

改为使用：

UPDATE part p
SET    active       = i.active, 
       notes        = i.notes,      
       weight       = i.weight, 
       condition_id = i.condition_id
FROM  (
   SELECT DISTINCT ON (part_num)
          part_num, active, notes, weight, condition_id
   FROM   importedDocument
   ORDER  BY part_num, <column / expression to define which row to pick>
   ) i
WHERE  p.part_num = i.part_num;

DISTINCT ON 的更多信息答案：

选择第一行在每个GROUP BY组中？

如果您没有提供其他 ORDER BY 项目<列/表达式以定义要选择的行> 的查询仍然有效，但是Postgres可以从多个匹配项中任意选择要选择的行。

If you don't provide additional ORDER BY items in place of <column / expression to define which row to pick>, the query still works, but Postgres decides arbitrarily which row to pick from multiple matches.

这篇关于从SELECT结果更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！