CODEWITHSUNDEEP
sqlpostgresqlsql-updategreatest-n-per-group
Damian
Damian

Reputation: 461

UPDATE from result of SELECT

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.

Upvotes: 0

Views: 224

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

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.

Upvotes: 2

Related Questions