SQL Server
Safe UPDATE and DELETE Practices in SQL Server
Use a repeatable SQL Server workflow to preview rows, count targets, make changes in a transaction, verify results, and choose COMMIT or ROLLBACK.
On this page
An UPDATE or DELETE can change more rows than you intended if you use the wrong database or filter. The safest beginner habit is a sequence: confirm the database, preview the exact target rows with SELECT, count them, make the change, inspect the result, and decide whether to commit or roll back. The earlier lessons introduced UPDATE and DELETE; this lesson combines them into a repeatable workflow. Practice only in the synthetic JaviBuildsLab database, never against production.
Confirm the database context
Query windows in SSMS can remain connected to a server while their selected database changes. Before any data change, ask SQL Server which database the current session is using:
SELECT DB_NAME() AS CurrentDatabase;For these exercises, the result should be JaviBuildsLab. If it is different, do not continue. Select the intended database in SSMS or, if appropriate for your environment, deliberately change context with USE JaviBuildsLab; and verify again. USE changes the context for the connection; it does not create the database or prove that the target tables contain the expected data. For Azure SQL Database, connect directly to the named database because USE is not supported there.
Preview the exact target
Write the SELECT first, using the same FROM table and WHERE condition that the later UPDATE or DELETE will use. For a single practice row, a primary key is usually a clear choice:
SELECT CustomerId, FirstName, LastName, City, State
FROM dbo.Customers
WHERE CustomerId = 11;Inspect the values, not just the fact that a row appeared. Confirm it is the intended customer and that the filter returns the expected number of rows. If the row is absent, the later statement would affect zero rows. If extra rows appear, refine the condition before changing anything. When you do not recognize the results, stop and ask what the data means rather than guessing.
Count the rows you expect to change
A count makes the scope easy to compare with your expectation:
SELECT COUNT(*) AS RowsToChange
FROM dbo.Customers
WHERE CustomerId = 11;If you expect one row and see 1, the count agrees with that expectation. If you see 0 or 1,000, stop and investigate. A count is a useful second check, not a replacement for looking at the actual rows. For a group change, run both the detail SELECT and COUNT with the exact same predicate. Verify that every returned row belongs in the change.
Prefer a primary key or another unique identifier when the intent is to change one row. A filter such as State = 'FL' may match many customers. That can be valid when all those rows are deliberately intended, but never assume the scope. Read the results, compare the count to the expected range, and make the condition more specific if needed. A syntactically correct WHERE clause can still describe the wrong business choice.
Use a transaction while practicing
A transaction groups statements into a unit that can be committed or rolled back. For a learning exercise, keep the transaction open while inspecting the changed result, then choose ROLLBACK so the practice database returns to its earlier state:
BEGIN TRANSACTION;
UPDATE dbo.Customers
SET City = N'Orlando'
WHERE CustomerId = 11;
SELECT @@ROWCOUNT AS RowsUpdated;
SELECT CustomerId, City FROM dbo.Customers WHERE CustomerId = 11;
ROLLBACK TRANSACTION;
SELECT CustomerId, City FROM dbo.Customers WHERE CustomerId = 11;The first SELECT after UPDATE reads @@ROWCOUNT immediately, before another statement replaces it. The next SELECT displays the temporary value inside the transaction. ROLLBACK cancels the uncommitted change, and the final SELECT lets you confirm the previous value is back. If the target row did not exist, the UPDATE affects zero rows; the transaction still needs to be closed.
ROLLBACK TRANSACTION only reverses work that is still inside an open transaction. It is not a general undo command after COMMIT. If the session disconnects or an error occurs, SQL Server has transaction rules for resolving the open work, but beginners should not rely on that as a practice plan. Finish deliberately and check the transaction state.
Decide carefully between COMMIT and ROLLBACK
COMMIT TRANSACTION makes the transaction’s changes permanent from the session’s point of view. Use it only after confirming the database, target rows, expected count, changed values, and intended outcome, and only when you are authorized to make that change. ROLLBACK TRANSACTION discards the uncommitted work. While learning, default to ROLLBACK. The diagram below emphasizes that a correct result may proceed to COMMIT, while a wrong or uncertain result should be rolled back.
@@TRANCOUNT reports how many BEGIN TRANSACTION statements are active in the current session. After a successful COMMIT or ROLLBACK of the outer transaction, it should return to zero:
SELECT @@TRANCOUNT AS OpenTransactionCount;If the count is greater than zero after your exercise, a transaction remains open. Close it intentionally before moving on. XACT_STATE() is another SQL Server function that reports whether the current transaction is active and committable, uncommittable, or absent. It can help when handling errors, but this beginner workflow does not need error-handling logic; knowing to check and close the transaction is enough for now.
Keep the target and verification specific
The WHERE condition used to preview, count, and change rows should match. For a single record, prefer its primary key. After the change, read the affected row again and compare it with your intended result. Use @@ROWCOUNT immediately after UPDATE or DELETE to see how many rows the statement affected, then run the verification SELECT. A one-row count does not prove that you selected the correct person, and a correct-looking value does not prove that other rows were untouched. Both checks matter.
Large changes need more planning than one beginner example. Do not turn an all-table update into a casual exercise or improvise a batch strategy. Review the target, expected scope, and approved process with an experienced owner before a large change. Performance and batch design are separate advanced topics; this lesson focuses on correctness and safe verification.
Transactions are useful safeguards, but they are not substitutes for backups, a recovery strategy, authorization, or organizational change control. A committed change may require a restore or another reviewed recovery procedure. In a production environment, follow your organization’s approval, backup, and review requirements. These lessons use only JaviBuildsLab and the synthetic rows prepared for practice.
What to do when the checks disagree
Treat any mismatch as a stop signal. If DB_NAME() shows another database, correct the connection before querying or changing anything. If SELECT displays an unexpected customer, do not edit the WHERE clause in the UPDATE and run immediately; revise the preview, then review the new result. If the COUNT is larger than expected, identify the extra rows. If it is zero, confirm that the intended practice row exists. If @@ROWCOUNT differs from the preview count, stop before any follow-up change and investigate why the target set changed or the statements differed.
A preview and change are separate statements, so the data could be changed by another session between them. In a beginner exercise on a local demo database, this is unlikely to be a concern, but it is useful to understand that SELECT followed by UPDATE is not a universal guarantee that nothing else can affect the target. Do not try to solve concurrency or isolation topics from this lesson. In a shared or production environment, follow a reviewed process designed for that system and involve its owner.
Keep the transaction short and focused during practice. Open it immediately before the change, inspect the result, and close it with ROLLBACK. Do not leave an SSMS query window with an open transaction while moving on to unrelated work. Other actions in the same session can become part of that transaction, and an unclosed transaction can make it harder to understand what has been changed. @@TRANCOUNT gives you a simple check before you leave the exercise.
If the statement encounters an error, read the error and inspect the transaction state before deciding what to do next. XACT_STATE() can show whether an active transaction can still be committed or must be rolled back, but it is not a substitute for understanding the error. For these lessons, the straightforward choice is to stop, use ROLLBACK when SQL Server permits it, verify @@TRANCOUNT, and compare the practice data with its expected state. Avoid repeatedly rerunning a failed or uncertain change.
The order of these checks matters. Confirming the database before the SELECT helps avoid inspecting a similarly named table elsewhere. Reusing the exact predicate reduces the chance that the reviewed rows and changed rows differ. Capturing @@ROWCOUNT immediately preserves the evidence for the statement you just ran. Reading the changed row makes the outcome visible, and closing the transaction prevents later work from accidentally joining the exercise. None of these checks is complicated on its own; together they make a small data change easier to understand and explain.
For beginners, the safest result is often a successful practice change that is then rolled back. You still learn how SQL Server evaluates the statement, what the affected row looks like, and how the transaction behaves, while returning the local demo data to its previous state. If an exercise does not produce the expected result, do not keep editing until it appears correct. Roll back, inspect the initial data again, and revise the query while no tentative changes remain open.
A rollback-first template
Use this as a review template after replacing the table, columns, and predicate with a statement you understand. Run the SELECT and COUNT first. Then begin the transaction, make one change, check the affected count immediately, and inspect the result. The template ends with ROLLBACK by default. COMMIT is present only as a commented decision point; do not uncomment it during routine practice.
SELECT DB_NAME() AS CurrentDatabase;
SELECT CustomerId, FirstName, LastName, City
FROM dbo.Customers
WHERE CustomerId = 11;
SELECT COUNT(*) AS RowsToChange
FROM dbo.Customers
WHERE CustomerId = 11;
BEGIN TRANSACTION;
UPDATE dbo.Customers
SET City = N'Orlando'
WHERE CustomerId = 11;
SELECT @@ROWCOUNT AS RowsChanged;
SELECT CustomerId, FirstName, LastName, City
FROM dbo.Customers
WHERE CustomerId = 11;
-- COMMIT TRANSACTION;
ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS OpenTransactionCount;For a DELETE exercise, replace the UPDATE with DELETE FROM dbo.Customers WHERE CustomerId = 11; and verify with a SELECT while the transaction is still open. The final ROLLBACK should restore the row. Do not run a copied template until you understand each predicate and the table it targets.
Common mistakes and practice
Common mistakes include starting a transaction and forgetting to close it, committing before verifying, believing ROLLBACK can undo a committed transaction, using a broad WHERE, being in the wrong database, copying SQL without understanding it, and testing destructive statements against production. Keep each action visible: confirm the database, inspect the rows, check the expected count, run one change, verify the result and count, then make an explicit decision. During practice, the decision should normally be ROLLBACK.
- What should
DB_NAME()show before these exercises? - Write a preview and count for customer 11.
- In the template, why is COMMIT commented and ROLLBACK active?
- What should
@@TRANCOUNTshow after the transaction has been rolled back? - Name two safeguards that a transaction does not replace.
Answers
-- 1
SELECT DB_NAME() AS CurrentDatabase;
-- 2
SELECT CustomerId, FirstName, LastName, City
FROM dbo.Customers WHERE CustomerId = 11;
SELECT COUNT(*) AS RowsToChange
FROM dbo.Customers WHERE CustomerId = 11;
-- 4
SELECT @@TRANCOUNT AS OpenTransactionCount;The expected database is JaviBuildsLab. The preview shows the values, while COUNT checks the number of matching rows. ROLLBACK is active so practice is discarded by default; COMMIT requires a deliberate, reviewed choice. After the transaction closes, @@TRANCOUNT should be zero. Backups, recovery planning, authorization, and change controls remain necessary safeguards.
This lesson completes the Changing Data Safely stage. Continue with common SQL beginner mistakes as the first Going Further lesson, or revisit WHERE filtering whenever you need to reason about a target condition.
- Confirm database with DB_NAME()
- SELECT target rows
- COUNT expected rows
- BEGIN TRANSACTION
- UPDATE or DELETE
- Verify result and @@ROWCOUNT
- Decision
- COMMIT when correct and authorized
- ROLLBACK when wrong or uncertain
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 19 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetSQL Server Performance Troubleshooting FlowUse a repeatable SQL Server troubleshooting flow to separate blocking, CPU, I/O, waits, query regressions, and workload pressure before changing anything.Open cheat sheet
- ArticleAggregate Functions in SQL Server: COUNT, SUM, AVG, MIN, and MAXLearn how SQL Server aggregate functions summarize customer and order rows with COUNT, SUM, AVG, MIN, and MAX.Read article
- RoadmapSQL Server FoundationsUse a repeatable SQL Server workflow to preview rows, count targets, make changes in a transaction, verify results, and choose COMMIT or ROLLBACK.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.