We have a database of around 5,000 products and use tier pricing. Updating prices is too time-consuming to do it manually so we do it on percentages.
I wrote this query a few years ago to update all products, but today I needed a filter to update products in specific categories only so I had to make some changes. There may be a better way to do this, but on my system, this takes around 7 seconds to run so I'm not concerned with making it more efficient for now.
Enjoy!
/*
---------------------Price update script below ------------------
-- Update Tier Prices for all products based on a percentage
-- Coded by Christopher W Navarro
-- License: Apache License 2.0
-- Filter products based on Category
-- The whole thing is commented out to protect agains accidental F5 presses
-- Select the portion of the query and hit Execute
-- I use a test system to test everything before hitting Production -- Almost always ;)
use <nopcommerce test db>
-- First we create a temp tale to hold our product Ids
create table #prodids (Id int)
go
insert into #prodids (Id)
-- Now we select our parent products based on the category Ids we need, also include nested categories
-- This shouls not be necessary but some of our Grouped products were single products first and may have had tier prices
select distinct p.Id
from product p
left outer join Product_Category_Mapping pcm
on p.id = pcm.ProductId
inner join category c
on c.id = pcm.CategoryId
where c.id in (1460,1416) or c.ParentCategoryId in (1460,1416)
union all
--Then we select the All child products
select distinct p.Id
from product p
left outer join Product_Category_Mapping pcm
on p.ParentGroupedProductId = pcm.ProductId
inner join category c
on c.id = pcm.CategoryId
where c.id in (1460,1416) or c.ParentCategoryId in (1460,1416)
union all
-- Here we are adding products with specific SKUs
-- Again, this one may not be necessary for you
select distinct p.id
from product p
where sku in ('XYZ', 'ABC')
union all
-- Now the children from above
select distinct id
from product where ParentGroupedProductId in (
select p.id
from product p
where sku in('XYZ', 'ABC')
)
Go
-- Finally we are going to make the update to tier prices, we're adding 5%
-- Since our products can be in multiple categories, we need the distinct clause here
update t
set t.Price = t.price+(t.price*0.05)
from TierPrice t
inner join product p
on t.ProductId = p.Id
where p.id in (select distinct id from #prodids)
GO
-- And here we update the product so the system does not freak out
-- If you see zero for all your tier prices on a product page,
-- then the Product price is not the same as the first tier price and this fixes it.
update p
set p.price = t.price
from product p
inner join TierPrice t
on p.id = t.ProductId
where p.HasTierPrices = 1
-- Don't forget to drop the temp table if you are doing multiple runs
-- drop table #prodids
-- the end!
*/