Update Records in SQL Statement
In software testing, when preparing test data, sometimes,we need to update existing records to change or replace test data. In SQL Server, Update statement can update or replace one or more rows in a table.
In this example below, we replace the Products with ListPrice=0.0 with ListPrice=1.00
Use AdventureWorks2008 Go --count number of products with zero list price select COUNT(*) as 'Number of Products with Zero Price' from Production.Product where ListPrice=0.0 --replace ListPrice=0.0 with ListPrice=1.00 update Production.Product set ListPrice=1.00 where ListPrice=0.0 --count number of products with zero list price (should return 0 records) select COUNT(*) as 'Number of Products with Zero Price' from Production.Product where ListPrice=0.0 --count number of products with ListPrice=1.0 (should return 200 rocords) select COUNT(*) as 'Number of Products with Updated price' from Production.Product where ListPrice=1.00
Query Result
Number of Products with Zero Price ---------------------------------- 200 (1 row(s) affected) (200 row(s) affected) Number of Products with Zero Price ---------------------------------- 0 (1 row(s) affected) Number of Products with Updated price ------------------------------------- 200 (1 row(s) affected)