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.