How to fill in missing records based on yesterday's information on SQL?-CodePudding

I am getting daily inventory information from a store and storing it onto an SQL table. The store only posts the products that have any sort of change but I need a complete list of all the products. Luckily I was able to retrieve the entire catalogue one day, so I thought if I can persist the data from yesterday's information where those product's are not in today's information then I would be good. Except, I don't know how to do that.

Example:

My table

Product   |  Inventory   |  Date
---------------------------------------
  A       |   1          | 2022-12-07
---------------------------------------
  B       |   2          | 2022-12-07
---------------------------------------
  C       |   3          | 2022-12-07
---------------------------------------
  D       |   4          | 2022-12-07
---------------------------------------
  E       |   5          | 2022-12-07

What the store posts:

Product   |  Inventory   |  Date
---------------------------------------
  A       |   6          | 2022-12-08
---------------------------------------
  B       |   1          | 2022-12-08

The rows I need as a result:

Product   |  Inventory   |  Date
---------------------------------------
  A       |   6          | 2022-12-08
---------------------------------------
  B       |   1          | 2022-12-08
---------------------------------------
  C       |   3          | 2022-12-08
---------------------------------------
  D       |   4          | 2022-12-08
---------------------------------------
  E       |   5          | 2022-12-08

CodePudding user response：

This might be a case for LEFT JOIN and COALESCE, such as:

select
    coalesce( u.product, p.product ) as product,
    coalesce( u.inventory, p.inventory ) as inventory,
    coalesce( u.date, p.date) as date
from
    all_products p
    left join
    store_updates u on u.product = p.product

Keep in mind this will only work correctly if store_updates table only has the latest row for a product. If you insert all store updates then you will need extra logic to get only the latest row per product.