Changing and removing rows: UPDATE and DELETE

UPDATE changes values in existing rows. SET says which columns get which new values, and WHERE says which rows. The WHERE clause is exactly the one you know from SELECT:

1UPDATE dbo.Products2SET Price = 19.99, Stock = Stock + 103WHERE ProductId = 1;4 5SELECT @@ROWCOUNT AS RowsUpdated;6 7SELECT ProductId, Name, Price, Stock FROM dbo.Products WHERE ProductId = 1;

RowsUpdated
-----------
          1

ProductId  Name         Price  Stock
---------  -----------  -----  -----
        1  Claw hammer  19.99     50

Stock = Stock + 10 reads the old value and writes a new one. Every expression in SET sees the row as it was before the update, so the order of the assignments does not matter. @@ROWCOUNT tells you how many rows the last statement touched; read it straight away, because the next statement replaces it.

1SELECT ProductId, Name, CategoryId, Price, Stock, Unit2FROM dbo.Products3WHERE CategoryId = 1;

The missing WHERE

Without WHERE, UPDATE and DELETE affect every row in the table. It is the most expensive typo in SQL, and SSMS has no undo button. Three habits protect you:

1. Write the SELECT first. Write SELECT * FROM dbo.Products WHERE …, check the rows, then change only the first line into UPDATE … SET … or DELETE.
2. Check the row count. SSMS prints “(n rows affected)”. If you expected 3 and it says 12, stop.
3. Use a transaction when the change matters, so you can look before you commit.

A transaction as a safety net

BEGIN TRANSACTION starts a unit of work. Nothing is final until COMMIT; ROLLBACK undoes everything since BEGIN. Here a price rise is checked, found to be wrong, and undone:

1BEGIN TRANSACTION;2 3UPDATE dbo.Products SET Price = Price * 1.10;   -- oops: no WHERE4 5SELECT TOP (3) Name, Price FROM dbo.Products ORDER BY ProductId;6 7ROLLBACK;   -- undo it8 9SELECT TOP (3) Name, Price FROM dbo.Products ORDER BY ProductId;

Name              Price
----------------  -----
Claw hammer       20.35
Cordless drill    97.90
Tape measure 8 m  10.73

Name              Price
----------------  -----
Claw hammer       18.50
Cordless drill    89.00
Tape measure 8 m   9.75

OUTPUT: old and new values

In an UPDATE, OUTPUT can return each row’s value before (deleted.) and after (inserted.) the change, a built-in audit of what you did:

1UPDATE dbo.Products2SET Price = Price * 1.103OUTPUT inserted.ProductId, deleted.Price AS OldPrice, inserted.Price AS NewPrice4WHERE CategoryId = 1;

ProductId  OldPrice  NewPrice
---------  --------  --------
        1     18.50     20.35
        2     89.00     97.90
        3      9.75     10.73

The new prices are rounded to two decimals as they are stored in the decimal(10,2) column: 9.75 × 1.10 = 10.725 becomes 10.73.

DELETE

DELETE FROM table WHERE … removes whole rows. Ella (customer 5) has never placed an order, so she can be removed:

1DELETE FROM dbo.Customers2WHERE CustomerId = 5;3 4SELECT @@ROWCOUNT AS RowsDeleted;

RowsDeleted
-----------
          1

Asha (customer 1) has orders. Her orders point at her through a foreign key, and SQL Server will not leave them pointing at a customer who no longer exists:

1DELETE FROM dbo.Customers2WHERE CustomerId = 1;

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Orders__Customer__3E52440B". The conflict occurred in database "BuildMart", table "dbo.Orders", column 'CustomerId'.

The strange constraint name was made up by SQL Server, because the setup script did not name the foreign key; on your server the hex digits at the end will differ. Foreign keys are a safety net for DELETE in the same way that types are a safety net for INSERT. The Constraints lesson shows how to choose what happens instead (for example, deleting the orders too).

DELETE or TRUNCATE?

1UPDATE dbo.Orders2SET Status = 'Cancelled'3WHERE Status = 'Pending' AND OrderDate < '2024-06-01';4 5SELECT @@ROWCOUNT AS RowsUpdated;6 7SELECT OrderId, OrderDate, Status FROM dbo.Orders;

RowsUpdated
-----------
          1

OrderId  OrderDate   Status
-------  ----------  ---------
      1  2024-02-03  Shipped
      2  2024-02-10  Shipped
      3  2024-03-01  Shipped
      4  2024-03-15  Shipped
      5  2024-04-02  Shipped
      6  2024-04-20  Cancelled
      7  2024-05-05  Shipped
      8  2024-05-18  Cancelled
      9  2024-06-01  Pending
     10  2024-06-12  Shipped