问题

我正在进行一次UPDATE操作，该操作应更新与SELECT查询返回的行数相同的行。

UPDATE items
SET quantity = items.quantity - (subquery.order_quantity * subquery.join_quantity)
FROM (
    SELECT x.id_recipe as id_recipe,
    x.quantity as order_quantity, 
    join_recipes_items.quantity as join_quantity,
    join_recipes_items.id_item as id_item
    FROM json_to_recordset(${JSON.stringify(recipes_fixed)}::json) as x("id_recipe" int, "quantity" decimal(30,10))
    JOIN join_recipes_items USING(id_recipe)
) as subquery
WHERE items.id_item = subquery.id_item;

最后一个UPDATE语句应该使用SELECT返回的值来满足WHERE条件来更新所有行，但实际上只更新了一些行。

如果将SELECT语句拆分以查看其结果：

SELECT x.id_recipe as id_recipe,
    x.quantity as order_quantity, 
    join_recipes_items.quantity as join_quantity,
    join_recipes_items.id_item as id_item
FROM json_to_recordset(${JSON.stringify('[ { "id_recipe": 2, "quantity": 3 }, { "id_recipe": 3, "quantity": 2 } ]')}::json) as x("id_recipe" int, "quantity" decimal(30,10))
JOIN join_recipes_items USING(id_recipe);

使用'[ { "id_recipe": 2, "quantity": 3 }, { "id_recipe": 3, "quantity": 2 } ]'作为示例输入：

该结果是正确的，但UPDATE仅使用行1、2、5来更新items，实际上应该使用所有行。可能出现了什么问题？

<details>
<summary>英文:</summary>

I&#39;m making an UPDATE that should update as many rows as the SELECT query returns.
       

UPDATE items
SET quantity = items.quantity - (subquery.order_quantity * subquery.join_quantity)
FROM (
SELECT x.id_recipe as id_recipe,
x.quantity as order_quantity,
join_recipes_items.quantity as join_quantity,
join_recipes_items.id_item as id_item
FROM json_to_recordset(${JSON.stringify(recipes_fixed)}::json) as x("id_recipe" int, "quantity" decimal(30,10))
JOIN join_recipes_items USING(id_recipe)
) as subquery
WHERE items.id_item = subquery.id_item;

The last UPDATE should update all rows that achieve the WHERE condition with the values given by the SELECT but it only does some updates.

If you take the SELECT statement apart to see its result:

SELECT x.id_recipe as id_recipe,
x.quantity as order_quantity,
join_recipes_items.quantity as join_quantity,
join_recipes_items.id_item as id_item
FROM json_to_recordset(${JSON.stringify('[ { "id_recipe": 2, "quantity": 3 }, { "id_recipe": 3, "quantity": 2 } ]')}::json) as x("id_recipe" int, "quantity" decimal(30,10))
JOIN join_recipes_items USING(id_recipe);

Using `'[ { "id_recipe": 2, "quantity": 3 }, { "id_recipe": 3, "quantity": 2 } ]'` as an example input:

[![Result of the SELECT query](https://i.stack.imgur.com/UFiRB.png)](https://i.stack.imgur.com/UFiRB.png)

That result is correct but the UPDATE is only using the rows 1,2,5 to update items when it should use all the rows.

What could be happening?


</details>


# 答案1
**得分**: 2

以下是从[PostgreSQL 15文档，Update命令][1]中的内容：

> 当存在FROM子句时，基本上发生的情况是目标表与from_item列表中提到的表进行连接，连接的每个输出行表示目标表的更新操作。在使用FROM时，应确保连接为每个要修改的行生成最多一个输出行。换句话说，目标行不应与其他表中的多行连接。如果是这样，那么只有一个连接行将用于更新目标行，但不容易预测将使用哪个连接行。

> 由于这种不确定性，只在子查询内引用其他表通常更安全，尽管通常比使用连接更难阅读和较慢。

这段代码演示了这种行为：

```sql
CREATE TABLE items (
  id integer PRIMARY KEY,
  quantity integer
);

INSERT INTO items (id, quantity)
  VALUES (1, 5);

UPDATE items
   SET quantity = quantity + delta
  FROM (VALUES (1, -1), (1, -2)) deltas (id, delta)
 WHERE items.id = deltas.id;

SELECT * FROM items;

最终结果将是quantity要么是4要么是3，取决于deltas中的哪个元组首先匹配。对于这个示例，quantity几乎肯定将为4。

以下是汇总要由subquery返回的值，以便quantity的最终值将是正确的示例：

UPDATE items
   SET quantity = items.quantity - subquery.used_quantity
  FROM
    (SELECT SUM(x.quantity * join_recipes_items.quantity) AS used_quantity,
            join_recipes_items.id_item AS id_item
       FROM json_to_recordset(${JSON.stringify(recipes_fixed)}::json) AS x("id_recipe" int, "quantity" decimal(30, 10))
       JOIN join_recipes_items USING (id_recipe)
      GROUP BY join_recipes_items.id_item) AS subquery
 WHERE items.id_item = subquery.id_item;

CREATE TABLE items (
  id integer PRIMARY KEY,
  quantity integer
);

INSERT INTO items (id, quantity)
  VALUES (1, 5);

UPDATE items
   SET quantity = quantity + delta
  FROM (VALUES (1, -1), (1, -2)) deltas (id, delta)
 WHERE items.id = deltas.id;

SELECT * FROM items;

UPDATE items
   SET quantity = items.quantity - subquery.used_quantity
  FROM
    (SELECT SUM(x.quantity * join_recipes_items.quantity) AS used_quantity,
            join_recipes_items.id_item AS id_item
       FROM json_to_recordset(${JSON.stringify(recipes_fixed)}::json) AS x("id_recipe" int, "quantity" decimal(30, 10))
       JOIN join_recipes_items USING (id_recipe)
      GROUP BY join_recipes_items.id_item) AS subquery
 WHERE items.id_item = subquery.id_item;