Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting a many-to-many relationship to one-to-many in PostgreSQL

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

I'd now like to model this as a one-to-many (which my data allows).

I've added a foo_id column to bar but now I want to migrate my data. So, I want to

UPDATE bar SET foo_id = f where id = b;

where each f and b pair are coming from

SELECT foo_id AS f, bar_id AS b FROM foo_bar;

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

I know how to do sub-SELECTs in UPDATEs when there's only one value, but stumped how to do it in this case.

like image 828
James Tauber Avatar asked Aug 28 '12 01:08

James Tauber


People also ask

How do you change a many-to-many relationship?

To avoid this problem, you can break the many-to-many relationship into two one-to-many relationships by using a third table, called a join table. Each record in a join table includes a match field that contains the value of the primary keys of the two tables it joins.

How do you handle a many-to-many relationship in SQL?

When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.


3 Answers

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

If you should have multiple rows for one bar (which you shouldn't, according to your description) the one row will be updated multiple times and the result is arbitrary.

This form of the query generally performs better than a correlated subquery.

Note that the primary key of bar should really be named bar_id - I use that name in the query.

like image 183
Erwin Brandstetter Avatar answered Oct 16 '22 18:10

Erwin Brandstetter


You can still join tables in UPDATE statements, 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 simply as

UPDATE  bar a
SET     foo_id = c.foo_id
FROM    foo_bar c
WHERE   a.id = c.bar_id
like image 21
John Woo Avatar answered Oct 16 '22 17:10

John Woo


If you really have a 1-many relationship, then it doesn't matter which value of foo you take for a given bar -- there is only one or they are all 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)

The subquery limits the results to a single value.

like image 2
Gordon Linoff Avatar answered Oct 16 '22 17:10

Gordon Linoff