在PostgreSQL中将多对多关系转换为一对多关系 [英] Converting a many-to-many relationship to one-to-many in PostgreSQL

问题描述

我有一个多对多的 foo 和 bar 建模为一个表 foo_bar 与 foo_id 和 bar_id 。

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).

我添加了一个 foo_id 列到 bar ，但现在我要迁移我的数据。所以，我想要

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;

其中每个 f 和 b pair来自

where each f and b pair are coming from

SELECT foo_id AS f, bar_id AS b FROM foo_bar;

可以在SQL（特别是PostgreSQL 9.0）中执行此操作吗？

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

我知道如何在UPDATE中执行子选择，只有一个值，但在这种情况下，如何处理。

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

推荐答案

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.

请注意， bar 的主键应该真的被命名为 bar_id - 我在查询中使用该名称。

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

这篇关于在PostgreSQL中将多对多关系转换为一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！