SQL to update prices

Hello, We have a store running on version 3.4 and need help with the SQL code to update several thousand products by 10% overall (for everything). Therefore we need to update;
Product price
Product old price
Product special price
Product attribute type Price adjustment (where exists)
Attribute combinations Overridden price (where exists)
Any help is appreciated,
Many thanks, James

update product set price = replace(price, price, (price*1.1))

Have you tried something like that? I'm not in front of my SSMS so just guessing for now. Of course you'd try on a backup copy first.

Hello, Thank you for your help, so if I need to update all the products with attibute combinations would this work?

update product set price = replace(priceAdjustment, priceAdjustment, (price*1.1))
And;

update product set price = replace(OverridenPrice, OverridenPrice, (price*1.1))
Many thanks again.

jamesg wrote:
This should do it:

BEGIN TRANSACTION

UPDATE Product
   SET Price = Price * 1.1
      ,OldPrice = OldPrice * 1.1
OUTPUT inserted.Id, deleted.Price, inserted.Price, deleted.OldPrice, inserted.OldPrice

UPDATE Product
   SET SpecialPrice = SpecialPrice * 1.1
OUTPUT inserted.Id, deleted.SpecialPrice, inserted.SpecialPrice
 WHERE SpecialPrice IS NOT NULL

UPDATE ProductAttributeValue
   SET PriceAdjustment = PriceAdjustment * 1.1
OUTPUT inserted.Id, deleted.PriceAdjustment, inserted.PriceAdjustment

UPDATE ProductAttributeCombination
   SET OverriddenPrice = OverriddenPrice * 1.1
OUTPUT inserted.Id, deleted.OverriddenPrice, inserted.OverriddenPrice
 WHERE OverriddenPrice IS NOT NULL

ROLLBACK

That's based on a 3.6 database so check the table and column names before doing it but I think it'll be the same (and change the ROLLBACK to COMMIT once you're happy it's not going to break anything).

You might also want to round the prices back to 2 decimal places after the calculation as nop is able to store 4dp by default:

Price = ROUND(Price * 1.1, 2)

Many thanks Pete,
That worked brilliant, for V3.4 I just needed to change the table names to include Variant for ProductVariantAttributeValue and ProductVariantAttributeCombination as follows;

BEGIN TRANSACTION

UPDATE Product
   SET Price = Price * 1.1
      ,OldPrice = OldPrice * 1.1
OUTPUT inserted.Id, deleted.Price, inserted.Price, deleted.OldPrice, inserted.OldPrice

UPDATE Product
   SET SpecialPrice = SpecialPrice * 1.1
OUTPUT inserted.Id, deleted.SpecialPrice, inserted.SpecialPrice
WHERE SpecialPrice IS NOT NULL

UPDATE ProductVariantAttributeValue
   SET PriceAdjustment = PriceAdjustment * 1.1
OUTPUT inserted.Id, deleted.PriceAdjustment, inserted.PriceAdjustment

UPDATE ProductVariantAttributeCombination
   SET OverriddenPrice = OverriddenPrice * 1.1
OUTPUT inserted.Id, deleted.OverriddenPrice, inserted.OverriddenPrice
WHERE OverriddenPrice IS NOT NULL

COMMIT

If any one needs to update all prices by a set percentage, this works! Thanks again Pete.
Best regards,
James