Update records based on several common columns

• ray805

  Valued Member

  Points: 64

  More actions

  March 20, 2025 at 11:41 am

  Go to Answer

  #4563742

  I have 4 tables (apologies for the formatting of the tables,  I don't know how to better show it)

  Order lines table:

  | Id | RateTierIds | price|
  | -----|-------------|---|
  | 1 | 1 | 20 |
  | 2 | 2 | 30 |

   

  Rate plans table:

  | Id | Name |
  | ---|--------|
  | 1 | Old Rate Plan|
  | 2 | New Rate Plan|
  | 3 | Other Rate Plan|

  Rate Plan Tiers:

  | Id | RatePlanIDNumber | Price | MinQty | BundleId|
  | ---|------------------|-------|----|--|
  | 1 | 1 | 20 | 1 | 1|
  | 2 | 1 | 30 | 2 | 2|
  | 3 | 2 | 20 | 1 | 1|
  | 4 | 2 | 30 | 2 | 2|
  | 5 | 3 | 34 | 4 | 5|

  Bundles table:

  | BundleId | Name|
  | ----------|------|
  | 1 | blah|
  | 2 | another|
  | 5 | another|

  I created 'New Rate Plan' which is an exact copy of Old Rate Plan. I want to change in OrderLines table for any RateTierIds that belong to 'Old Rate Plan' to the RateTierIds of the 'New Rate Plan'. But based on the common Bundle ID, Price, Qty, etc to make sure I am putting the same tier that is in the old one.

  I was trying something like this:

   

  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' ???

  Desired Results in Order Lines Table (The RateTierIds should be the ids of 'New Rate Plan' but based on same price, qty, bundle etc.

  RateTierIds are now ids of 'New Rate Plan')

  | Id | RateTierIds | price|
  | ---|-------------|-----|
  | 1 | 3 | 20 |
  | 2 | 4 | 30 |

  Any ideas? Thanks.

• Mary_Jen

  Grasshopper

  Points: 15

  More actions

  March 20, 2025 at 2:38 pm

  Answer

  #4563849

  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 2 days ago by  Mary_Jen.

• ray805

  Valued Member

  Points: 64

  More actions

  March 20, 2025 at 7:43 pm

  #4564013

  Thanks Mary_Jen!  You saved me a lot of time, that worked!