Safe UPDATE: Preview, Transaction, Verify
sqlSQLbeginnerinterview-prepbest-practicessql-server
A SQL Server habit that prevents the classic disaster of updating every row: preview the rows, change them inside a transaction, check the count, then commit or roll back.
-- SQL Server (T-SQL).
-- 1. Look at exactly the rows you are about to change.
SELECT id, name, salary
FROM employees
WHERE department_id = 2;
-- 2. Make the change inside a transaction. Never forget the WHERE clause.
BEGIN TRANSACTION;
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 2;
-- 3. Is that the number of rows you expected?
SELECT @@ROWCOUNT AS rows_updated;
-- 4. Run ONE of these next:
-- COMMIT TRANSACTION; -- the count and the data look right
-- ROLLBACK TRANSACTION; -- anything unexpected: nothing has changed
Comments
Loading comments…