Skip to content
Advertisement

Updating specific rows to values based on the count of rows in another table

I have a table RESERVED_BOOKINGS_OVERRIDDEN

booking_product_id          on_site_from_dt         on_site_to_dt            venue_id
     4               2021-08-07 16:00:00.000      2021-08-14 10:00:00.000        12
     4               2021-08-07 16:00:00.000      2021-08-10 10:00:00.000        12
     6               2021-08-02 16:00:00.000      2021-08-09 10:00:00.000        12

and another table ALLOCATED_PRODUCTS

         Date                 booking_product_id        venue_id              ReservedQuant
2021-08-05 00:00:00.000        4                        12                      3   
2021-08-06 00:00:00.000        4                        12                      3   
2021-08-07 00:00:00.000        4                        12                      3   
2021-08-08 00:00:00.000        4                        12                      3   
2021-08-05 00:00:00.000        6                        12                      1

Now I need to update the ReservedQuant column in the ALLOCATED_PRODUCTS table based on the rows in RESERVED_BOOKINGS_OVERRIDDEN

The ReservedQuant must minus by the amount of rows found where the ALLOCATED_PRODUCTS.Date is within the RESERVED_BOOKINGS_OVERRIDDEN.on_site_from_dt and RESERVED_BOOKINGS_OVERRIDDEN.on_site_to_dt and ALLOCATED_PRODUCTS.booking_product_id = RESERVED_BOOKINGS_OVERRIDDEN.booking_product_id.

This should be the state of the data after the update:

     Date                 booking_product_id        venue_id              ReservedQuant
2021-08-05 00:00:00.000        4                        12                      3   
2021-08-06 00:00:00.000        4                        12                      3   
2021-08-07 00:00:00.000        4                        12                      1   
2021-08-08 00:00:00.000        4                        12                      1   
2021-08-05 00:00:00.000        6                        12                      0

Advertisement

Answer

update a set a.ReservedQuant=ReservedQuant-(select count(1) from RESERVED_BOOKINGS_OVERRIDDEN b where a.booking_product_id=b.booking_product_id
and a.date between cast(b.on_site_from_dt as date) and cast(b.on_site_to_dt as date)) 
from ALLOCATED_PRODUCTS a
Advertisement