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

Question

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   

question from:https://stackoverflow.com/questions/65643857/updating-specific-rows-to-values-based-on-the-count-of-rows-in-another-table

Answer 1

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