基于公共字段更新OrderLines表关联旧费率计划的RateTierIds

现有表结构 #

1. Order Lines表 #

2. Rate Plans表 #

3. Rate Plan Tiers表 #

4. Bundles表 #

需求说明 #

已创建与「Old Rate Plan（旧费率方案）」完全一致的「New Rate Plan（新费率方案）」，需要将OrderLines表中所有属于旧费率方案的RateTierIds，替换为新费率方案中具有相同BundleId、Price、MinQty的对应层级ID。

尝试的SQL语句 #

update l set
    l.RatePlanBundleTierId = t.Id
from OrderLines as l
inner join 'Rate Plan Tiers' t
    on (l.RatePlanBundleTierId = t.Id)
inner join dbo.Bundles b
    on (t.BundleId = b.Id)
inner join dbo.RatePlans r
    on (r.Id = t.RatePlanId)
-- Not sure below how to do the constraints?
where t.BundleId = b.Id
    and r.[Name] = 'Old Rate Plan' ???

期望更新后的Order Lines表 #

解决方案 #

需要关联两次Rate Plan Tiers表：一次定位旧费率方案的层级信息，另一次匹配新费率方案中对应条件的层级ID。具体SQL如下：

UPDATE ol
SET ol.RateTierIds = new_t.Id
FROM OrderLines ol
-- 关联旧费率方案的层级与方案信息
JOIN [Rate Plan Tiers] old_t ON ol.RateTierIds = old_t.Id
JOIN RatePlans old_rp ON old_t.RatePlanIDNumber = old_rp.Id
-- 通过匹配条件关联新费率方案的层级
JOIN [Rate Plan Tiers] new_t 
    ON old_t.BundleId = new_t.BundleId
    AND old_t.Price = new_t.Price
    AND old_t.MinQty = new_t.MinQty
JOIN RatePlans new_rp ON new_t.RatePlanIDNumber = new_rp.Id
WHERE old_rp.Name = 'Old Rate Plan'
  AND new_rp.Name = 'New Rate Plan';

逻辑说明 #

1. 先通过OrderLines关联旧费率方案的层级表和方案表，筛选出所有归属旧方案的订单行；
2. 再用BundleId、Price、MinQty三个核心条件，匹配到新费率方案中对应的层级记录，获取其ID；
3. 最后将订单行中原有的旧层级ID替换为新层级ID。

内容的提问来源于stack exchange，提问作者ray805