0

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.

nmac
  • 610
  • 1
  • 10
  • 20

1 Answers1

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;
Community
  • 1
  • 1
surfinokie
  • 41
  • 3
  • This should have been a duplicate flag, not an answer. With a bit more rep, [you will be able to flag duplicates](http://stackoverflow.com/privileges/comment). – Nathan Tuggy Feb 05 '15 at 00:49
  • the solution in the other post didn't solve my problem, so it shouldn't be categorized as a duplicate just yet... – nmac Feb 05 '15 at 00:53