Update Data

Data changes. Prices are adjusted, order statuses progress from processing to shipped to delivered, customer membership tiers are upgraded, and stock quantities decrease with every sale. The UPDATE statement is how SQL Server modifies existing rows — changing one or more column values for any set of rows that match a WHERE condition. It is one of the most powerful statements in T-SQL and one of the most dangerous when used carelessly. An UPDATE without a WHERE clause modifies every single row in the table. An UPDATE with an incorrect WHERE clause silently corrupts thousands of rows with no error message. This lesson covers every form of the UPDATE statement, the verification habits that prevent mistakes, and the transaction pattern that makes updates safely reversible — all using the DataplexaStore tables.

Single Column UPDATE

The most basic UPDATE sets one column to a new value for rows matching a WHERE condition. The SET clause lists the column and its new value. The WHERE clause identifies which rows to change. Before running any UPDATE in production the cardinal rule is to run the equivalent SELECT first — use the exact same WHERE clause to confirm that the rows it targets are exactly the rows you intend to change. The number of rows affected reported by the SELECT must match your expectation before you run the UPDATE. This single habit prevents the most common category of data corruption mistakes.

-- Always verify with SELECT before running UPDATE
-- Step 1: SELECT with the same WHERE to confirm the target rows
USE DataplexaStore;

SELECT ProductID, ProductName, Price
FROM Products
WHERE ProductName = 'Wireless Mouse';

-- Step 2: run the UPDATE only after confirming the SELECT result
UPDATE Products
SET    Price = 32.99
WHERE  ProductName = 'Wireless Mouse';

-- Step 3: verify the change
SELECT ProductID, ProductName, Price
FROM Products
WHERE ProductName = 'Wireless Mouse';

• The (1 row affected) message confirms exactly one row was changed — always check this count matches your expectation; if it says 0 your WHERE matched nothing, if it says 500 when you expected 1 something is wrong
• Filtering by ProductName rather than ProductID is less safe — if two products had the same name both would be updated; always prefer the primary key in UPDATE WHERE clauses when targeting a specific row
• The verify SELECT after the UPDATE confirms the new value is stored correctly — a quick check that costs milliseconds and confirms the operation succeeded as intended

Multi-Column UPDATE

A single UPDATE statement can change multiple columns simultaneously by listing them comma-separated in the SET clause. This is always preferable to running multiple separate UPDATE statements for the same row — a single UPDATE is one atomic operation that either succeeds completely or fails completely, whereas multiple separate UPDATEs can partially succeed if an error occurs between them, leaving the row in an inconsistent intermediate state. There is no limit to the number of columns that can be SET in a single UPDATE.

-- Update multiple columns in a single statement — atomic, all-or-nothing
-- Upgrade a user's membership tier and record the change cleanly

-- Verify first
SELECT UserID, FullName, MembershipTier
FROM Users
WHERE UserID = 4;

-- Update both columns in one statement
UPDATE Users
SET    MembershipTier = 'Premium'
WHERE  UserID = 4;

-- Update an order — status advances and we want to confirm the total is correct
UPDATE Orders
SET    Status       = 'shipped',
       TotalAmount  = 42.98       -- recalculate total if needed
WHERE  OrderID = 5;

-- Verify both changes
SELECT UserID, FullName, MembershipTier FROM Users  WHERE UserID = 4;
SELECT OrderID, Status, TotalAmount     FROM Orders WHERE OrderID = 5;

• Updating Status and TotalAmount together in one statement is safer than two separate UPDATEs — if the connection dropped between two separate statements the order could end up as shipped with the wrong TotalAmount
• The SET clause columns can be listed in any order — SQL Server applies all assignments simultaneously rather than sequentially, so SET A = B, B = A swaps values correctly without needing a temporary variable
• Always filter on the primary key (UserID = 4, OrderID = 5) when updating a specific row — primary key lookups use the clustered index directly and are both fast and precise

UPDATE with Expressions

The new value in a SET clause does not have to be a literal — it can be any valid T-SQL expression including arithmetic using the column's current value, string functions, date calculations, or a subquery. Expressions based on the current column value are the correct way to apply relative changes — increase a price by 10%, decrease stock by a quantity sold, append a suffix to a string. Using the column's own name in the expression reads the current value before any change is applied, computes the new value, and writes it back. All rows targeted by the WHERE clause are updated simultaneously using their pre-update values, so expressions are safe even when updating many rows at once.

-- UPDATE with expressions — relative changes using current column values

-- Apply a 5% price increase to all Home Office products
UPDATE Products
SET    Price = Price * 1.05             -- multiply current Price by 1.05
WHERE  Category = 'Home Office';

-- Decrease stock for a product that just sold 3 units
UPDATE Products
SET    StockQty = StockQty - 3
WHERE  ProductID = 1;                   -- Wireless Mouse sold 3 units

-- Verify the Home Office price changes
SELECT ProductName, Category, Price
FROM   Products
WHERE  Category = 'Home Office'
ORDER BY Price DESC;

-- Verify the stock change
SELECT ProductName, StockQty
FROM   Products
WHERE  ProductID = 1;

• Price * 1.05 applies to each row's current Price independently — Standing Desk was 349.99 × 1.05 = 367.49, Ergonomic Chair was 249.99 × 1.05 = 262.49, Desk Lamp was 34.99 × 1.05 = 36.74
• StockQty - 3 reads the current value (42 from the initial seed, already adjusted to 39 after the earlier sale demonstration) and subtracts 3 — the result is written atomically, no intermediate state is visible to other sessions
• The CHECK constraint CK_Products_Stock enforces StockQty >= 0 — if you tried to sell more units than are in stock the UPDATE would fail with Msg 547, preventing negative inventory automatically

UPDATE with JOIN

SQL Server supports updating a table based on values from a related table by using a JOIN in the UPDATE statement. This pattern is used when the new value for a column must come from another table — for example, recalculating an order total from its line items, or applying a discount rate stored in a separate pricing table. The T-SQL syntax uses FROM and JOIN after the SET clause, which is different from the ANSI standard UPDATE syntax but is the form that SQL Server uses. The table being updated must be aliased in the FROM clause and that alias must appear in the SET clause's table reference.

-- UPDATE with JOIN — recalculate each Order's TotalAmount from its OrderItems

-- First see the current totals vs the calculated totals
SELECT
    o.OrderID,
    o.TotalAmount                           AS stored_total,
    SUM(oi.Quantity * oi.UnitPrice)         AS calculated_total
FROM   Orders o
JOIN   OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.OrderID, o.TotalAmount
ORDER BY o.OrderID;

-- Update Orders.TotalAmount to match the sum of its line items
UPDATE o
SET    o.TotalAmount = oi_totals.line_total
FROM   Orders o
JOIN (
    SELECT   OrderID, SUM(Quantity * UnitPrice) AS line_total
    FROM     OrderItems
    GROUP BY OrderID
) AS oi_totals ON o.OrderID = oi_totals.OrderID;

-- Verify the updated totals
SELECT OrderID, TotalAmount FROM Orders ORDER BY OrderID;

• Orders 1 and 3 had small discrepancies between the stored total and the sum of line items — a common real-world data quality issue where totals were captured at order creation and line items were later adjusted
• The subquery in the JOIN aggregates OrderItems before joining to Orders, ensuring each OrderID maps to a single calculated total — without the aggregation the join would produce multiple rows per order and the UPDATE would fail or produce unpredictable results
• The alias o in UPDATE o identifies which table in the FROM clause is being updated — this is required syntax when using FROM and JOIN in a T-SQL UPDATE

UPDATE with OUTPUT

The OUTPUT clause captures both the old and new values of updated rows, returning them as a result set or inserting them into a table variable. This is the cleanest way to build an audit trail — recording what each row contained before and after an UPDATE without requiring a separate SELECT. OUTPUT DELETED contains the row values before the UPDATE (the old state), and OUTPUT INSERTED contains the row values after the UPDATE (the new state). Both virtual tables are available simultaneously, making it straightforward to record the before and after values of any changed column in a single statement.

-- UPDATE with OUTPUT — capture before and after values for audit logging

DECLARE @PriceChangeLog TABLE (
    ProductID       INT,
    ProductName     NVARCHAR(100),
    OldPrice        DECIMAL(10,2),
    NewPrice        DECIMAL(10,2),
    ChangedAt       DATETIME2 DEFAULT GETDATE()
);

-- Apply a 10% discount to Electronics and capture the changes
UPDATE Products
SET    Price = ROUND(Price * 0.90, 2)    -- 10% discount, rounded to 2 decimal places
OUTPUT
    DELETED.ProductID,                   -- old row values (before update)
    DELETED.ProductName,
    DELETED.Price       AS old_price,
    INSERTED.Price      AS new_price     -- new row values (after update)
INTO @PriceChangeLog (ProductID, ProductName, OldPrice, NewPrice)
WHERE  Category = 'Electronics';

-- Review the audit log
SELECT
    ProductID,
    ProductName,
    OldPrice,
    NewPrice,
    NewPrice - OldPrice     AS price_change,
    ChangedAt
FROM @PriceChangeLog
ORDER BY ProductID;

• DELETED and INSERTED are virtual tables that exist only within the OUTPUT clause — DELETED holds the pre-update values and INSERTED holds the post-update values of the same rows
• All five Electronics products were updated and logged in a single statement — no separate SELECT before or after, no second round trip, no possibility of a mismatch between the update and the audit record
• ROUND(Price * 0.90, 2) prevents floating-point rounding accumulation — DECIMAL arithmetic is exact but the multiplication can produce more decimal places than the column stores, so ROUND ensures the stored value matches the logged value exactly

Safe UPDATE Pattern — Transactions

For any UPDATE that modifies multiple rows or updates critical data, wrapping it in an explicit transaction with a pre-flight check gives you a safety window. The pattern is: BEGIN TRANSACTION, run the UPDATE, check the row count with @@ROWCOUNT, and either COMMIT if the count matches expectations or ROLLBACK if it does not. This approach catches the classic mistake of an UPDATE affecting the wrong number of rows before the change is committed to disk. The transaction holds locks on the affected rows until COMMIT or ROLLBACK, so this pattern should be kept brief — acquire, check, commit or rollback, move on.

-- Safe UPDATE pattern with transaction and ROWCOUNT check

BEGIN TRANSACTION;

    UPDATE Products
    SET    StockQty = 0
    WHERE  Category = 'Stationery'
      AND  StockQty < 50;            -- intentionally narrow WHERE for demonstration

    -- @@ROWCOUNT holds the number of rows affected by the most recent statement
    IF @@ROWCOUNT = 0
    BEGIN
        PRINT 'No rows matched — rolling back';
        ROLLBACK TRANSACTION;
    END
    ELSE IF @@ROWCOUNT > 5          -- sanity check — more than 5 would be unexpected
    BEGIN
        PRINT 'Too many rows affected — rolling back';
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        PRINT CAST(@@ROWCOUNT AS VARCHAR) + ' row(s) updated — committing';
        COMMIT TRANSACTION;
    END

-- Verify the result
SELECT ProductName, Category, StockQty
FROM   Products
WHERE  Category = 'Stationery'
ORDER BY ProductName;

• Only Ballpoint Pen Set (StockQty = 200 initially but reset to 0 by this update) matched the WHERE — Notebook A5 has StockQty = 120 which is not less than 50, so it was correctly excluded
• @@ROWCOUNT must be read immediately after the statement it measures — the next statement resets it; storing it in a variable (DECLARE @affected INT = @@ROWCOUNT) is safer when more logic follows
• The sanity check upper bound (> 5) is specific to the business context — for an update that should only ever touch one specific product, the check would be IF @@ROWCOUNT <> 1

Summary Table

Practice Questions

Quiz

Next up - Delete Data - Remove rows safely, understand the difference between DELETE and TRUNCATE, and avoid the irreversible mistakes.