Update row data with dynamic lag

Question

I have a table of items and restaurants. We assume that if the restaurant entry is missing, it has to be completed with the last non-null restaurant value in the previous rows (as defined by ascending id values).

CREATE TABLE food_items (
  id SERIAL PRIMARY KEY,
  item VARCHAR(255) NOT NULL,
  restaurant VARCHAR(255)
);
INSERT INTO food_items(item, restaurant) 
VALUES ('Salad','McDonalds'), ('Burger',''),('Fries',''),('Salad','Taco Bell'),('Quesadilla','');

The tricky part is that the number of rows for each restaurant is variable, so it cannot be captured with a fixed lag.

score 0 · Answer 1 · answered Jan 03 '24 at 10:25

You seem to be confounding empty string ('') and null. Both are very much distinct.

Assuming null values where your example shows empty strings.

UPDATE food_items f
SET    restaurant = f1.r1
FROM  (
   SELECT id, first_value(restaurant) OVER (PARTITION BY grp ORDER BY id) AS r1 
   FROM  (
      SELECT *, count(restaurant) over (ORDER BY id) AS grp
      FROM   food_items
      ) sub
   ) f1
WHERE  f.id = f1.id
AND    f.restaurant IS NULL;  -- avoid empty updates

fiddle

See:

About avoiding empty updates:

How do I (or can I) SELECT DISTINCT on multiple columns?

Update row data with dynamic lag

1 Answers1