Changing data: INSERT, UPDATE and DELETE
Reading data is half the story. This lesson covers adding rows with INSERT, changing them with UPDATE and removing them with DELETE, how to get a new row's id, and a safe routine that prevents accidentally changing every row in a table.
- StartWrite the WHERE as a SELECTSELECT * FROM Orders WHERE OrderId = 42
- Step 2Check the rowsMake sure only the rows you expect are returned
- Step 3Start a transactionBEGIN TRANSACTION
- Step 4Run UPDATE or DELETEUse exactly the same WHERE clause
- Step 5Check the row countSQL Server reports how many rows were affected
- EndCommit or roll backCOMMIT if correct, ROLLBACK if not
The idea in plain words
INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows. UPDATE and DELETE affect every row that matches their WHERE clause, and without a WHERE clause that means every row in the table. That is why careful developers always test the WHERE clause with a SELECT first.
Example
-- Add one row and return its new id
INSERT INTO Customers (Name, Email)
OUTPUT INSERTED.CustomerId
VALUES (N'Asha Rao', N'[email protected]');
-- Add several rows at once
INSERT INTO Products (Name, Price)
VALUES (N'Pen', 20.00), (N'Notebook', 80.00);
-- Change matching rows
UPDATE Products
SET Price = Price * 1.10
WHERE Category = N'Stationery';
-- Remove matching rows, safely
BEGIN TRANSACTION;
DELETE FROM Orders WHERE OrderId = 42;
-- check: (1 row affected)
COMMIT; -- or ROLLBACK; if the count is wrongSoft delete
Many apps never physically delete important records. Instead they set a flag such as IsDeleted = 1 or a DeletedAt date and filter those rows out of queries. This keeps history and makes mistakes reversible. EF Core can apply such a filter automatically with global query filters.
Common mistakes
- Running UPDATE or DELETE without WHERE.
- Deleting a parent row that other rows reference; the foreign key will block it unless you handle children first.
- Using SELECT MAX(Id) to get a new id, which breaks when two users insert at once. Use OUTPUT or SCOPE_IDENTITY().
- Running many single-row inserts in a loop when one multi-row insert or a bulk method would do.
Interview question
How do you get the id of a row you just inserted in SQL Server?
Use the OUTPUT clause, as in INSERT ... OUTPUT INSERTED.CustomerId VALUES (...), which returns the generated values directly, or call SCOPE_IDENTITY() right after the insert in the same scope. Avoid @@IDENTITY, which can return ids generated by triggers, and never use SELECT MAX(Id), which is unsafe under concurrency. EF Core sets the generated key on the entity after SaveChanges.
Want the full details? Read the official Microsoft documentation for this topic.