Convert many-to-many to one-to-many relationships in PostgreSQL

Question

I have many, many between foo and bar , modeled as a table foo_bar with foo_id and bar_id .

Now I would like to simulate this as one-to-many (which my data allows).

I added the foo_id column to bar , but now I want to transfer my data. So i want

 UPDATE bar SET foo_id = f where id = b; 

where each pair f and b comes from

 SELECT foo_id AS f, bar_id AS b FROM foo_bar; 

Is it possible to do this in SQL (and in particular PostgreSQL 9.0)?

I know how to do sub-SELECT in UPDATE when there is only one value, but to a dead end how to do it in this case.

Answer 1

 UPDATE bar b SET foo_id = fb.foo_id FROM foo_bar fb WHERE fb.bar_id = b.bar_id; 

If you should have several lines for one bar (which you should not, according to your description), one line will be updated several times, and the result will be arbitrary.

This query form usually works better than a correlated subquery.

Note that the primary key bar really needs to be named bar_id - I use this name in the request.

Answer 2

You can still join tables in UPDATE , try

 UPDATE bar a SET foo_id = c.foo_id FROM ( SELECT foo_id, bar_id FROM foo_bar ) c WHERE a.id = c.bar_id 

or just like

 UPDATE bar a SET foo_id = c.foo_id FROM foo_bar c WHERE a.id = c.bar_id 

Answer 3

If you really have a 1-to-many relationship, it doesn't matter what foo you take for a given bar - there is only one or all are the same.

You can do the following:

 update bar set foo_id = (select max(foo_id) from foo_bar where foo_bar.bar_id = bar.id) 

A subquery limits the results to a single value.