You’re on the right track with your update query, but you need to match records based on the correct attributes like Price, MinQty, and BundleId while switching to the new RatePlanId. Try this approach:
UPDATE ol
SET ol.RateTierIds = rpt_new.Id
FROM OrderLines ol
JOIN RatePlanTiers rpt_old ON ol.RateTierIds = rpt_old.Id
JOIN RatePlans rp_old ON rpt_old.RatePlanIDNumber = rp_old.Id AND rp_old.Name = 'Old Rate Plan'
JOIN RatePlans rp_new ON rp_new.Name = 'New Rate Plan'
JOIN RatePlanTiers rpt_new
ON rpt_new.RatePlanIDNumber = rp_new.Id
AND rpt_new.Price = rpt_old.Price
AND rpt_new.MinQty = rpt_old.MinQty
AND rpt_new.BundleId = rpt_old.BundleId;
This ensures that only corresponding records are updated while maintaining correct tier relationships.
- This reply was modified 7 months, 3 weeks ago by Mary_Jen.