Mark,

Try (not tested)

UPDATE dbo.orders
	SET source_code = ph.source_code

FROM dbo.orders AS o
JOIN dbo.vPromotions AS ph 
	ON ph.mid = o.mid AND ph.issue_date  = (
		SELECT MAX(issue_date) FROM dbo.vPromotions ph2
			WHERE ph2.mid = o.mid 
				AND ph2.issue_date <= o.issue_date)  
WHERE NOT EXISTS (SELECT * FROM dbo.vPromotions ph3
			WHERE ph3.mid = o.mid 
				AND ph3.source_code = o.source_code)

>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
>   )
>