UPDATE doesn't update all the rows

Question

I'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:

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?

That should have been *... 3 and 4 are not being updated?*. To your question add the manual calculation and it's results. — Adrian Klaver, Apr 06 '23 at 22:16
Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [mre] [ask] [Help] — philipxy, Apr 06 '23 at 22:47
Once a row has been updated, that row will not be updated again regardless of any other rows from the subquery that satisfy the matching criteria. This is intended behavior. The post's initial assertion that "an UPDATE ... should update as many rows as the SELECT query returns" is incorrect. The number of rows in the table that have matches to rows in the subquery should be updated. — JohnH, Apr 06 '23 at 23:08
1) So is there an `item_id = 1` in `items`? 2) In `items` what is the value for `quantity`? If there is only one row with `item_id=2` then the `quantity` will be the result of the cumulative updates of line 2 and 5 from the subquery. — Adrian Klaver, Apr 07 '23 at 19:01

score 2 · Answer 1 · answered Apr 08 '23 at 03:39

The following is from PostgreSQL 15 Documention, Update Command:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

This code demonstrates the behavior:

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;

The end result will be that quantity is either 4 or 3, depending on which of the tuples in deltas is matched first. For this example, it is all but certain that quantity will be 4.

The following aggregates the values to be returned by subquery so that the final values for quantity will be correct:

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;

UPDATE doesn't update all the rows

1 Answers1