Modify Existing Records Using the UPDATE Statement

Data in the VirtualBookShelf.com Web site changes constantly. New customers sign up while others become inactive, or a customer's phone number changes after a move. Book inventory changes every time a sale goes through, and a title's price might change when it moves from regular price to a sale price. None of this is handled by SELECT, which only reads rows; changing a row that already exists is the job of a different statement.

The UPDATE statement modifies rows already present in a table. A single UPDATE statement can change one row, several rows, or every row in the table, depending entirely on how it's written.

• Clauses used with UPDATE
  Like the SELECT and ORDER BY statements covered earlier in this module, UPDATE uses clauses to specify which columns change and which rows are affected:
  1. SET: names the column or columns to change, and the new value to store in each one
  2. WHERE: narrows the update to specific rows, exactly as it narrows a SELECT
  
  That second clause deserves its own warning before anything else: WHERE is not required by the UPDATE statement's syntax, but leaving it out updates every single row in the table. SQL Server has no way to guess that a missing WHERE clause was an accident rather than an intentional, table-wide change. Writing the WHERE clause first, before even deciding what to put in SET, is a habit worth building early, since it's much easier to add columns to update than to undo a change that landed on rows it should never have touched.
  
  The following series of figures builds an UPDATE statement piece by piece, the same way earlier lessons built up SELECT, WHERE, and ORDER BY.

UPDATE table

SET column1 = value,
column2 = value

UPDATE table
SET column1 = value,
column2 = value

UPDATE Inventory

SET QuantityOnHand = QuantityOnHand - 1

WHERE Title = 'The Last Lighthouse'

UPDATE Inventory
SET QuantityOnHand = QuantityOnHand - 1
WHERE Title = 'The Last Lighthouse'

Running an UPDATE statement in Azure Data Studio follows the same steps as running the SELECT statements earlier in this module, with one habit worth adding. Connect to the SQL Server 2025 instance, open a new query window against the VirtualBookShelf database, and type the statement. Before pressing Execute, though, it's worth running the WHERE clause's condition as a plain SELECT first, SELECT Title, QuantityOnHand FROM Inventory WHERE Title = 'The Last Lighthouse';, to confirm it actually matches the row intended and nothing else. Only after that confirms the right row, or rows, run the real UPDATE statement.

Once executed, Azure Data Studio's Messages pane reports how many rows were affected, for example "(1 row affected)." That single line is often the only confirmation an UPDATE statement gives; unlike SELECT, there's no result grid to scroll through unless the statement also includes an OUTPUT clause, covered later in this lesson. A query that reports "(0 rows affected)" when a change was expected is a signal to check the WHERE clause before assuming the update actually worked.

Figure 7's SET QuantityOnHand = QuantityOnHand - 1 is such a common pattern, take a column's current value, adjust it, store it back, that T-SQL provides a shorter way to write it. Compound assignment operators combine the column reference and the arithmetic into one symbol: +=, -=, *=, /=, and %=, for addition, subtraction, multiplication, division, and remainder respectively. Figure 7's SET clause can be rewritten as:

UPDATE Inventory
SET QuantityOnHand -= 1
WHERE Title = 'The Last Lighthouse'

This produces exactly the same result as figure 7; QuantityOnHand -= 1 means precisely what QuantityOnHand = QuantityOnHand - 1 means, it's a shorthand, not a different operation. Compound assignment operators have been part of T-SQL since SQL Server 2008, so this isn't new syntax, just a more compact way to write a statement this lesson's own example already needed.

SET can also assign a plain literal value rather than an expression based on the current one, exactly as figures 1 and 2 showed in general form. SET QuantityOnHand = 20 would reset the column to a fixed number regardless of what it held before, useful for correcting a count after a physical inventory recount, rather than adjusting it relative to a purchase.

Figures 8 and 9 show the Inventory table's state before and after the update separately, as two screenshots taken at different times. SQL Server can return both states from the single statement that makes the change, using the OUTPUT clause. OUTPUT references two special, temporary tables that exist only during the statement: deleted, holding each affected row's values before the change, and inserted, holding the same rows' values after it.

UPDATE Inventory
SET QuantityOnHand -= 1
OUTPUT deleted.Title, deleted.QuantityOnHand AS QtyBefore, inserted.QuantityOnHand AS QtyAfter
WHERE Title = 'The Last Lighthouse';

Running this statement against the Inventory table shown in figure 8 returns one row: The Last Lighthouse, 9, 8, the same before and after values figures 8 and 9 show across two separate tables, captured atomically in a single execution. OUTPUT is not new to SQL Server 2025; it has been part of T-SQL since SQL Server 2005, but it fits naturally here because it answers exactly the question this lesson's figure sequence answers by hand, what changed, and to what.

OUTPUT is also commonly used to write affected rows into a separate audit table, by adding an INTO clause after OUTPUT, so that every change to Inventory leaves a permanent record of its own before-and-after values without any extra application code. That use is beyond what this lesson needs, but it's worth knowing the capability exists once an application actually needs to track every change a table receives.

SQL Server 2025 adds native regular expression support to T-SQL, including a function tailor-made for exactly the kind of cleanup an UPDATE statement often performs: REGEXP_REPLACE. It takes a string, a pattern to match, and a replacement, and returns the string with every match replaced. Its full syntax accepts a few more optional arguments, a starting position, which occurrence to replace, and matching flags such as case-insensitivity, but the three-argument form covers most ordinary cases.

Suppose a customer's phone number was entered inconsistently in VirtualBookShelf's Customer table, as 555-123-4567 rather than the format the application expects. REGEXP_REPLACE can reformat it in place, using numbered backreferences, \1, \2, and \3, to refer back to the three groups the pattern captures:

UPDATE Customer
SET Phone = REGEXP_REPLACE(Phone, '(\d{3})-(\d{3})-(\d{4})', '(\1) \2-\3')
WHERE CustomerID = 17;

The pattern (\d{3})-(\d{3})-(\d{4}) matches three digits, a hyphen, three more digits, another hyphen, and four final digits, capturing each digit group in parentheses. The replacement, (\1) \2-\3, rearranges those same three captured groups into a different format, (555) 123-4567, without the application ever having to parse the string itself in C# or any other language. This is a genuinely new capability as of SQL Server 2025; earlier versions had no built-in regular expression functions at all, so the same cleanup would have required a CLR function, a loop of nested REPLACE calls, or handling it entirely in application code before the update even reached the database.

This module has compared SELECT, WHERE, and ORDER BY across raw SQL, ADO.NET, and Entity Framework Core. An UPDATE fits the same comparison, and the same caution about SQL injection from Lesson 3 applies here too, arguably more so, since an unparameterized UPDATE doesn't just leak data to an attacker, it can let one change it.

-- Raw SQL, run directly in Azure Data Studio
UPDATE Inventory
SET QuantityOnHand -= 1
WHERE Title = 'The Last Lighthouse';

// ADO.NET: never concatenate the title into the SQL text
using var cmd = new SqlCommand(
    "UPDATE Inventory SET QuantityOnHand -= 1 WHERE Title = @title;", connection);
cmd.Parameters.AddWithValue("@title", "The Last Lighthouse");
int rowsAffected = cmd.ExecuteNonQuery();

// Entity Framework Core: modify a tracked entity, then save
var book = context.Inventory.Single(b => b.Title == "The Last Lighthouse");
book.QuantityOnHand -= 1;
context.SaveChanges();

The EF Core version doesn't call anything that looks like UPDATE at all. It fetches the entity, changes a property on the C# object in memory, and calls SaveChanges(), which compares the entity's current values against the values it had when it was loaded and generates an UPDATE statement automatically for whatever changed. Underneath, that generated statement has the same WHERE clause shape as the raw SQL and ADO.NET versions above it; EF Core just builds it for you once it knows which single row the tracked entity represents.

cmd.ExecuteNonQuery() is used here instead of ExecuteReader(), which the earlier SELECT examples used. UPDATE doesn't return rows, so there's no reader to iterate over; ExecuteNonQuery() instead returns an integer, the number of rows the statement actually changed, which is useful on its own for confirming a statement found and updated exactly the row it was supposed to.

What happens if the WHERE clause in an UPDATE statement matches no rows at all? Nothing goes wrong, and nothing changes. SQL Server simply reports that zero rows were affected; ExecuteNonQuery() would return 0 in that case. This is worth checking in application code, since a WHERE clause matching zero rows often means a typo in a title, or a row that was already deleted, rather than a successful update.

Does SQL Server lock the whole table while an UPDATE runs? Not necessarily. SQL Server 2025 introduced Optimized Locking, which uses per-transaction locking and accelerated database recovery to avoid holding locks on rows an UPDATE statement's WHERE clause doesn't even match, reducing how much a single update statement blocks other activity on the same table. This happens automatically; none of this lesson's examples need to be written any differently to benefit from it.

Can one UPDATE statement change a very large number of rows efficiently? UPDATE TOP (n) limits a single execution to a specific number of rows, which is often combined with a WHILE loop to update a huge table in smaller batches rather than one enormous transaction. This is real T-SQL syntax, but batching and loops are beyond what this lesson's single-row examples need; it's worth knowing the capability exists for the day a table grows large enough to require it.

Can an UPDATE statement use values from a second table? Yes, using UPDATE ... FROM combined with a join, but that requires joining two tables together, a topic a later lesson in this course will cover. Every example in this lesson updates Inventory or Customer using only values already present in the row being changed.

Does SQL Server 2025 support updating JSON or vector data in place? Yes, SQL Server 2025's native JSON type supports in-place editing with JSON_MODIFY(), and vector columns used for AI similarity search can be updated the same way as any other column. VirtualBookShelf's schema doesn't currently use either data type, so neither applies to the examples in this lesson, but both are available for an application that stores that kind of data.

Is there a way to preview which rows an UPDATE statement will affect, before actually running it? Yes, informally: run the same condition as a SELECT first, exactly as the previous section describes. SELECT Title, QuantityOnHand FROM Inventory WHERE Title = 'The Last Lighthouse'; shows exactly which row, or rows, the equivalent UPDATE statement's WHERE clause would reach, without changing anything. This is good practice before running an UPDATE with an unfamiliar or complex WHERE clause, especially one built from user input.

Can more than one column be changed in the same UPDATE statement? Yes. The SET clause accepts a comma-separated list, exactly as figures 2 and 3 showed in general form. SET QuantityOnHand -= 1, Price = 22.99 would decrease the quantity and change the price in the same single statement, applied to whichever rows the WHERE clause selects. There's no limit on how many columns a single SET clause can touch, as long as every column belongs to the table named in the UPDATE clause.

What data type does QuantityOnHand need to be for a compound assignment operator like -= to work? Any numeric type supports the arithmetic compound operators, int in VirtualBookShelf's case. A text column only supports +=, which concatenates a string onto the end of the existing value rather than performing arithmetic; -=, *=, /=, and %= have no meaning for text and would raise an error if used against a column like Title.

Can an UPDATE statement change the column a table's primary key depends on, such as BookID? Technically yes, nothing in the UPDATE syntax itself forbids it, but it's rarely a good idea in practice. Other tables, and other application code, may already depend on a given BookID value staying the same over time; changing it after the fact can silently break those references rather than raising an obvious error. A column serving as a primary key is usually left alone once a row is created, with every other column remaining free to update as needed.

This lesson covered changing existing rows with UPDATE: the SET clause for naming new values, the WHERE clause for limiting which rows change, compound assignment operators as shorthand for a common pattern, the OUTPUT clause for capturing before-and-after values from one statement, and REGEXP_REPLACE for pattern-based text cleanup, new to SQL Server 2025.

In the next lesson, new rows will be added to a table using the INSERT statement, the last of this module's three ways to change data, alongside UPDATE for modifying rows and a future lesson's DELETE for removing them.