Price changes require care because incorrect UPDATE statements can modify every row, break reporting, or overwrite valid historical prices. This tutorial outlines a controlled update workflow.
Find the correct records
Start with a SELECT that uses the exact same predicate as the planned UPDATE. Verify the key, currency, effective date, and expected row count.
Transaction example
BEGIN TRANSACTION;
UPDATE dbo.Products
SET UnitPrice = 49.99
WHERE ProductID = 101;
SELECT @@ROWCOUNT AS RowsChanged;
-- Review the change before choosing:
-- COMMIT TRANSACTION;
-- or ROLLBACK TRANSACTION;Use constraints and parameters
The example assumes a Products table and is for a test database. In an application, pass ProductID and price as typed parameters. Add checks that prevent invalid negative or null prices if the domain requires them.
Protect history
For real businesses, consider a separate PriceHistory table with effective dates rather than overwriting the only recorded price. Capture actor, change reason, old and new values, and approval when required.
Deployment discipline
Test against a copy of the database, back up data according to policy, and never run a mass update in production without a verified WHERE clause and a rollback plan.
About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 73188). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.
