主页 / 问题 / 1194033
Accepted
Inventor
Asked:2021-10-23 01:44:15 +0000 UTC2021-10-23 01:44:15 +0000 UTC

以多对多关系更新数据库记录

  • 772

我正在学习 SQL。我有一个products_to_colors链接 2 个表colors和products多对多关系的表。包含字段id（只是id表中的记录，xs 我为什么要创建此列）product_id，，，color_id

 id | color_id | product_id
----+----------+-----------
1   | 1        | 7
----+----------+-----------
2   | 2        | 7
----+----------+-----------
3   | 3        | 7
----+----------+-----------
4   | 4        | 7

面临更新此数据库中数据的任务。让我们假设有一个新的请求进来，并且产品的颜色发生了变化，例如，id 为 7 的产品现在应该有颜色[1, 3, 5, 7]，它是[1, 2, 3, 4]。我无法为这样的任务编写足够的 SQL 查询。当然，你可以通过2个周期和PL（编程语言）（1个周期删除，2个添加），对数据库进行一堆查询，但这是一些狗屎代码，没有优化，我不想要要做到这一点。

很可能应该有 2 个对数据库的查询，第一个从 中删除不必要的链接products_to_colors，第二个添加必要的链接。

随着删除链接的请求，一切似乎或多或少都清楚了，我这样做了（我不知道它有多正确）：

DELETE FROM products_to_colors
    WHERE color_id = ANY (
            SELECT color_id FROM products_to_colors WHERE product_id=7
        )
        AND color_id != ANY('{1,3,5,7}')
        AND product_id=7;

仍然存在添加以前不存在的颜色的链接的功能。在这里我不知道该怎么做。好吧，我可以使用 PL 遍历带有 id 颜色列表的数组，并检查数据库中是否存在必要的连接，如果没有，则添加它。但这又是一堆请求和某种狗屎代码。

我找到了这样一个帖子，但情况略有不同，我不知道如何适应我的任务。对于 SQL（尤其是 postgres），互联网上的信息非常少，尤其是示例。不清楚如何学习编写好的代码 =)

sql

2 个回答

  1. 基本上它看起来像这样：

    WITH cte AS ( DELETE FROM products_to_colors
              WHERE (product_id, color_id) IN ( {список пар на удаление} )
            )
INSERT INTO products_to_colors VALUES {список пар на вставку} ;

    小提琴

    • 0
  2. Best Answer

    最后，我解决了这样的问题：

    DELETE FROM products_to_colors
WHERE color_id IN (
        SELECT color_id FROM products_to_colors WHERE product_id={product_id}
    )
    AND color_id != ALL({new_colors})
    AND product_id={product_id};

INSERT INTO products_to_colors (color_id, product_id)
    SELECT el, {product_id} FROM unnest({new_colors}) AS el WHERE el NOT IN (
        SELECT color_id FROM products_to_colors WHERE product_id={product_id}
    );

    其中：{new_colors}- 与产品关联的新颜色，{product_id}- 产品 ID

    • 0

相关问题