Sean, I told you I wasn't giving up. Here is my latest attempt.
with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName,
RiskNonRiskFlag, Region, MFG order by DeliveryDate, SerialNumber) as RowNum
from dbo.VehicleDetail
where ReturnDate Is Null
)
MERGE Vehicles AS v
USING ReturnsPros AS r
ON (r.ProgYear = v.ProgYear
and r.LeaseCycle = v.LeaseCycle
and r.ModelName = v.ModelName
and r.RiskNonRiskFlag = v.RiskNonRiskFlag
and r.Region = v.Region
and r.MFG = v.MFG and v.RowNum <= r.UnitCount)
WHEN MATCHED
THEN UPDATE SET v.ReturnDate = r.ReturnDate;
--did it actually work?
select * from VehicleDetail where ReturnDate is not null
You will notice your code before the merge statement. I was hoping I could get around the duplicate problem using merge but here is the message I received.
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.