Inserting values across tables with keys in Postgres

Question

I have two tables A and B. Table A has a column named food that is empty and a set of unique ids. Table B has the same matching set of ids, and a bunch of values for food. I want to get the values for food from B into A. That is, I want to move/copy the value for food that has id = 1 in table B to table A where id = 1, and so on for all values. How do I do this with postgres?

BTW, I know that I can use foreign keys and joins, thats not the question.

Community · Accepted Answer · 2017-05-23 10:29:33Z

1

I think Andrew Lazarus's answere here does exactly what you're looking for: updating table rows in postgres using subquery

It's simply an update from sub-select, non-standard SQL but brilliantly useful in Postgres.

In your case it might look something like:

UPDATE A
SET food=subquery.food
FROM (SELECT id, food
      FROM  B) AS subquery
WHERE A.id=subquery.id;

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 5, 2015 at 0:40

surfinokie

413 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan Tuggy Over a year ago

This should have been a duplicate flag, not an answer. With a bit more rep, you will be able to flag duplicates.

nmac Over a year ago

the solution in the other post didn't solve my problem, so it shouldn't be categorized as a duplicate just yet...

Collectives™ on Stack Overflow

Inserting values across tables with keys in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related