UPDATE from result of SELECT

Question

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

Table part has the following fields:

part_num PK
active 
notes
weight

Table importedDocument has the following fields:

part_num PK
active
notes
weight
quantity PK
condition_id  PK

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

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

Your table definition for `importedDocument` has multiple primary keys (PK), which is strictly impossible. Probably foreign keys? Please provide the output of `\d importedDocument` from `psql` instead, which also includes data types and other important information. Also, also *always*, your version of Postgres. — Erwin Brandstetter, Jul 26 '13 at 21:34
OK but why cant I have a composite primary key? The main idea of having composite key is that combination of 7 components of this composite primary key should be uniqe — Damian, Jul 29 '13 at 13:10
Of course you can have a composite pk. I misunderstood your representation. If you provide the table definition like advised such misunderstandings won't occur. Either way, including condition and quantity in the primary key is a rather uncommon design. I would consider a [`serial`](http://www.postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-SERIAL) column as surrogate primary key. — Erwin Brandstetter, Jul 29 '13 at 20:49

score 2 · Answer 1 · edited May 23 '17 at 10:24

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.

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.

Use instead:

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;

More information for DISTINCT ON in this related answer:
Select first row in each GROUP BY group?

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.

UPDATE from result of SELECT

1 Answers1

Linked