0

I am trying to map a contributor_id in my archive table to a contributor_id in my sales table. The archive table has 120k entries and the sales table has 20k. The sales table has an 'uploaded_filename' field, as does the archive table.

The sales table's uploaded_filenames are often repeated multiple times in the archive table.

My question is how do I write a query that maps the contributor_id in the archive to the corresponding uploaded_filename

Table Design (I'm omitting extraneous fields)

Archive:

| uploaded_filename | contributor_id |

Sales (at present):

| uploaded_filename |

My attempt:

UPDATE sales
SET contributor_id = a.contributor_id
FROM sales s
INNER JOIN archive a 
ON a.uploaded_filename = s.uploaded_filename;
GeneralBear
  • 1,011
  • 3
  • 11
  • 35
  • In Postgres's SQL , you should **not** repeat the target table in the `from` list. https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql/7869611#7869611 – wildplasser Oct 14 '18 at 21:38
  • If you remove the "FROM sales" line I think that's basically the query you want. – Hitobat Oct 14 '18 at 21:47
  • Why are you repeating data like *uploaded_filename* across related tables? One of the cornerstones of the relational database model is to avoid redundancy. – Parfait Oct 14 '18 at 21:52
  • Thanks for the help (this is all inherited data) --- I was able to follow https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql and solve it – GeneralBear Oct 14 '18 at 21:58
  • Also,your logic is flawed. There could be multiple *different* contributer_ids for the same updated_filename and you can choose only one for the update. – wildplasser Oct 14 '18 at 21:58

0 Answers0