SQL Server
UPDATE Basics in SQL Server
Learn to update SQL Server rows safely by previewing the target with SELECT, using a precise WHERE clause, and checking the result.
On this page
UPDATE changes values in rows that already exist. It does not add a row; that is what INSERT does. An UPDATE can affect one row or many, so the first habit is to write the filter as a SELECT and inspect its results. This lesson uses only the synthetic JaviBuildsLab data. Do not try examples on a production database.
Preview the row with SELECT first
Lesson 16 adds customer 11 as an optional practice row. If you completed it, inspect that row before changing anything:
SELECT
CustomerId,
FirstName,
LastName,
City,
State
FROM dbo.Customers
WHERE CustomerId = 11;Confirm that the result is the intended customer and that exactly one row appears. The WHERE condition identifies the target. If the query returns zero rows, stop: the practice row may not exist. If it returns more rows than expected, stop and refine the condition. Do not replace the filter with a guess or proceed just because the UPDATE is syntactically valid.
Once the preview is correct, use the same condition in the UPDATE:
UPDATE dbo.Customers
SET City = N'Tampa'
WHERE CustomerId = 11;UPDATE dbo.Customers names the table. SET gives the new value, and WHERE limits which rows receive that value. Because the preview used the same key, the intended result is to change Maya’s City to Tampa while leaving other customer rows alone. SQL Server reports the affected row count in SSMS, but read the row back as a separate verification step.
Change one or several columns
Use a comma-separated assignment list to change multiple columns on the same matching row:
UPDATE dbo.Customers
SET
City = N'Tampa',
State = 'FL'
WHERE CustomerId = 11;Each assignment names a column and its new value. The WHERE condition is still applied to the row, not separately to each assignment. Check that every value belongs to the selected customer and is valid for its column. City accepts Unicode text and State is two characters in this demo schema.
To remove a known email value, use SQL NULL, without quotes:
UPDATE dbo.Customers
SET Email = NULL
WHERE CustomerId = 11;This is different from assigning an empty string or the text 'NULL'. Before using it, confirm that the column allows NULL; the demo’s Email column does. A targeted UPDATE with a valid filter is one way to correct a value, but always preview first and verify afterwards.
WHERE controls the target rows
For a single-row task, a primary key such as CustomerId is usually a clear filter. Conditions based on a city or state can match many rows. This builds on filtering rows with WHERE: before changing a group, run the exact condition as a SELECT and count what it returns. If the result is broader than your intent, do not update it.
Dangerous demonstration only — do not run. This statement has no WHERE condition, so it targets every row in
dbo.Customers:
UPDATE dbo.Customers
SET State = 'FL';The example is shown to explain the risk, not to invite execution. Without WHERE, SQL Server applies the assignment to all rows. A missing or overly broad condition is one of the easiest ways to change data you did not mean to touch. Use the preview-and-compare workflow every time, even when the statement looks small.
Verify the change and check @@ROWCOUNT
After the UPDATE, select the row again using its key:
SELECT CustomerId, FirstName, LastName, City, State, Email
FROM dbo.Customers
WHERE CustomerId = 11;Confirm the displayed values are the ones you intended. To capture a statement’s affected count, read @@ROWCOUNT immediately after the UPDATE:
UPDATE dbo.Customers
SET City = N'Tampa'
WHERE CustomerId = 11;
SELECT @@ROWCOUNT AS RowsUpdated;@@ROWCOUNT reports rows affected by the immediately preceding statement. The SELECT that follows it replaces the previous statement’s count, which is why the check comes directly after the UPDATE. If you expected one row but the result is zero or more than one, stop and investigate. An affected count confirms how many rows matched; it does not confirm that the chosen values were correct, so inspect the resulting data too.
An UPDATE can intentionally match several rows. For example, a carefully reviewed practice change might set a value for rows in one state. First run SELECT CustomerId, State FROM dbo.Customers WHERE State = 'FL'; and inspect every row. Then use exactly that WHERE condition only if every returned row is meant to change. Since this alters several seeded practice records, do not leave it applied; use a transaction and rollback for experimentation. A condition’s row count is not a substitute for reviewing the rows themselves.
Simple expressions can calculate a new value from the current value, such as SET Amount = Amount + 10. This lesson does not use that pattern as a business recommendation. It demonstrates that SET expressions can read existing columns. Repeating such an UPDATE changes the value again, so preview and rollback practice changes; do not treat an example as a safe-to-repeat command.
Review the old value and the intended new value
Before an UPDATE, say in plain language what should change and what should remain the same. For customer 11, the intended change might be: “only Maya’s City becomes Tampa; her name, State, Email, and key stay as they are.” The preview query shows the current row, and the SET list expresses the new value. The WHERE clause defines the boundary between that customer and every other row. Keeping the goal this concrete makes it easier to notice an accidental extra assignment or a filter that identifies several people.
For a single row, review the old and new values together in your notes or in the query results. A change from Orlando to Tampa is understandable; a change to every person’s State is a very different action. If your requested outcome is “all Florida customers,” there may be a legitimate multi-row change, but you still need to inspect every row returned by the preview. Make sure the condition describes the intended set today, not just a set you remember from an earlier query.
When the set intentionally has multiple rows, an aggregate count can help you spot a mismatch, but the row list remains important. For example, if your practice SELECT for a group returns 3 customers and your expectation was 2, stop. Do not continue because the UPDATE affects only a small fraction of the table. Work out which row is unexpected, refine the predicate, and run the SELECT again. Repeat this until you can explain why each row qualifies.
An UPDATE that sets a column to the value it already contains can still report matching rows as affected, depending on the statement and SQL Server’s reporting behavior. Therefore, a nonzero @@ROWCOUNT is evidence about the statement’s target scope, not proof that the visible value changed. Compare the before and after values as well. This distinction prevents interpreting the affected count as a complete success message.
Do not use a SELECT alias as if it were a stored column name. The target table’s actual column names belong in UPDATE and SET; aliases only label query output. Similarly, avoid updating a primary key merely to make a record easier to recognize. Keys are referenced by relationships such as the foreign key from Orders to Customers, and a key change can affect or be blocked by those relationships. If a correction appears to require changing a key, stop and review the data model first.
If you are unsure whether the UPDATE is safe, do not run it yet. You can improve the preview, ask someone familiar with the dataset to review it, or use a rollback-first transaction in the local demo database. A transaction is useful for experimenting with a result before keeping it, but a narrow WHERE condition and a correct database context are still required. ROLLBACK cannot fix a statement that targeted the wrong database after you have committed it.
Common mistakes and practice
Common UPDATE mistakes include omitting WHERE, using the wrong key, changing the active database, skipping the SELECT preview, assuming the affected count is correct, writing 'NULL', and changing key columns without understanding their relationships. For a one-row change, a primary key is usually clearer than a descriptive attribute that several people may share. If a foreign key points to a key you want to change, SQL Server may reject the update; changing keys is not a casual correction.
Practice in JaviBuildsLab, and leave the data unchanged. The following exercise uses a transaction and ends with ROLLBACK. @@ROWCOUNT is captured before another statement can replace it:
SELECT CustomerId, City
FROM dbo.Customers
WHERE CustomerId = 11;
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 is your preview. Inside the transaction, the UPDATE changes the practice row temporarily. The result and count can be inspected, then ROLLBACK restores the value that existed before the transaction. Confirm the final SELECT. If customer 11 does not exist, the update affects zero rows; do not substitute a different ID without previewing it.
- Preview customer 11’s City before changing it.
- Write an UPDATE that changes City to Orlando and State to FL, using the same key as your preview.
- What does an UPDATE without WHERE target?
- Why should
SELECT @@ROWCOUNTimmediately follow the UPDATE? - How do you set Email to a missing value, and what must be true of that column?
Answers
-- 1
SELECT CustomerId, City FROM dbo.Customers WHERE CustomerId = 11;
-- 2
UPDATE dbo.Customers SET City = N'Orlando', State = 'FL'
WHERE CustomerId = 11;
-- 5
UPDATE dbo.Customers SET Email = NULL WHERE CustomerId = 11;An UPDATE without WHERE targets every row in the named table. @@ROWCOUNT must be read immediately because another statement changes the value it reports. Email must allow NULL. For hands-on practice, wrap the change in a transaction and finish with ROLLBACK. The next lesson covers DELETE, which removes rows and makes the same SELECT-first discipline even more important.
- SELECT target rows
- Verify exact match
- UPDATE with same WHERE
- Check @@ROWCOUNT
- SELECT again
Use the SQL Server Foundations roadmap to review the full learning sequence.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 17 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetSQL Server Wait TypesUse SQL Server wait statistics to identify where work is spending time, distinguish current waits from cumulative waits, and choose the next performance investigation.Open cheat sheet
- RoadmapSQL Server FoundationsLearn to update SQL Server rows safely by previewing the target with SELECT, using a precise WHERE clause, and checking the result.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.