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.

By JaviPublished 12 min readBeginner

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:

SQL query: Preview the row with SELECT firstT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    City,
    State
FROM dbo.Customers
WHERE CustomerId = 11;
T-SQL8 linesRead-only query display. It is not connected to a database.

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:

SQL query: Preview the row with SELECT first (2)T-SQL
UPDATE dbo.Customers
SET City = N'Tampa'
WHERE CustomerId = 11;
T-SQL3 linesRead-only query display. It is not connected to a database.

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:

SQL query: Change one or several columnsT-SQL
UPDATE dbo.Customers
SET
    City = N'Tampa',
    State = 'FL'
WHERE CustomerId = 11;
T-SQL5 linesRead-only query display. It is not connected to a database.

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:

SQL query: Change one or several columns (2)T-SQL
UPDATE dbo.Customers
SET Email = NULL
WHERE CustomerId = 11;
T-SQL3 linesRead-only query display. It is not connected to a database.

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:

SQL query: WHERE controls the target rowsT-SQL
UPDATE dbo.Customers
SET State = 'FL';
T-SQL2 linesRead-only query display. It is not connected to a database.

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:

SQL query: Verify the change and check @@ROWCOUNTT-SQL
SELECT CustomerId, FirstName, LastName, City, State, Email
FROM dbo.Customers
WHERE CustomerId = 11;
T-SQL3 linesRead-only query display. It is not connected to a database.

Confirm the displayed values are the ones you intended. To capture a statement’s affected count, read @@ROWCOUNT immediately after the UPDATE:

SQL query: Verify the change and check @@ROWCOUNT (2)T-SQL
UPDATE dbo.Customers
SET City = N'Tampa'
WHERE CustomerId = 11;

SELECT @@ROWCOUNT AS RowsUpdated;
T-SQL5 linesRead-only query display. It is not connected to a database.

@@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:

SQL query: Common mistakes and practiceT-SQL
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;
T-SQL16 linesRead-only query display. It is not connected to a database.

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.

  1. Preview customer 11’s City before changing it.
  2. Write an UPDATE that changes City to Orlando and State to FL, using the same key as your preview.
  3. What does an UPDATE without WHERE target?
  4. Why should SELECT @@ROWCOUNT immediately follow the UPDATE?
  5. How do you set Email to a missing value, and what must be true of that column?

Answers

SQL query: AnswersT-SQL
-- 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;
T-SQL9 linesRead-only query display. It is not connected to a database.

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.

  1. SELECT target rows
  2. Verify exact match
  3. UPDATE with same WHERE
  4. Check @@ROWCOUNT
  5. SELECT again
Preview the target before updating, then verify twice

Use the SQL Server Foundations roadmap to review the full learning sequence.

Check what you learned

2 quick questions

Question 1 of 2What does UPDATE change?
Question 2 of 2Why should you inspect the WHERE condition before running UPDATE?

SQL Server Foundations

Lesson 17 of 25

Continue to next lesson

DELETE Basics in SQL Server

Continue to Lesson 18

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL