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.
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With