I have a one to many relationship. For each Order in the Orders table find the most appropriate record in the Promotions view. Appropriate is defined as: closest vPromotions.issue_date to orders.demand_date that does not exceed the orders.demand_date, i.e., vPromotions.issue_date less than or equal to orders.demand_date

Parent table:

CREATE TABLE dbo.Orders 
(
  order_number varchar (26) NOT NULL ,
  mid int NULL , -- member id
  hid int NULL , -- household id
  demand_date smalldatetime NULL , -- order date
  source_code varchar (100)  NULL , -- reason for ordering
  ...
)

The Child table is a Union of 6 tables with the following column list

CREATE VIEW dbo.vPromotions
  ...
  mid int NULL ,
  hid int NULL ,
  issue_date smalldatetime NULL ,  -- date of promotion
  source_code varchar (100)  NULL, -- promotion campaign code
  ...

The problem: member has one or more records in the promotion tables but the reason for ordering does not match any promotion campaign codes, i.e., orders.mid = vPromotions.mid and orders.source_code < > vPromotions.source_code

Desired outcome: Update the Orders.source_code with the vPromotions.source_code that has the smallest date difference between orders.demand_date and vPromotions.issue_date

Close but not correct

UPDATE dbo.orders
SET source_code = ph.source_code

FROM dbo.orders AS o,
     dbo.vPromotions AS ph 
     JOIN
     ( 	SELECT mid, max(issue_date) issue_date 
	FROM dbo.vPromotions
	GROUP BY mid
      ) AS ph2
      ON ph.mid = ph2.mid AND ph.issue_date = ph2.issue_date
     
WHERE o.mid = ph.mid
   -- make sure the source_code for this mid is not in promo history
  AND CAST( o.mid as varchar(12))+ o.source_code NOT IN 
   (
    SELECT CAST( mid as varchar(12)) + source_code
    FROM kc.dbo.vPromotions
   )