07. Modifying Data -- UPDATE & DELETE Statements

Level: Beginner with caution

ℹ️ What You'll Learn

• UPDATE statement -- modify existing records
• Update single column or multiple columns
• Update with WHERE condition
• Update based on calculations
• DELETE statement -- remove records
• Delete with WHERE condition
• Common UPDATE & DELETE mistakes
• How to undo accidental deletes

INSERT adds data. SELECT reads it. UPDATE and DELETE modify it. This article teaches the final CRUD operations -- U (Update) and D (Delete).

Why UPDATE & DELETE Matter for Backend Developers

APIs need to modify and delete records:

Frontend sends: "Update student Ravi's class to 11-A"
    v
C# Backend API receives: PUT /students/1 {className: "11-A"}
    v
Backend calls: UPDATE Student SET ClassName = '11-A' WHERE StudentId = 1
    v
SQL Server updates one record
    v
Backend returns success response

---

Frontend sends: "Delete student Ravi"
    v
C# Backend API receives: DELETE /students/1
    v
Backend calls: DELETE FROM Student WHERE StudentId = 1
    v
SQL Server removes one record
    v
Backend returns success response

Without UPDATE and DELETE, your API is read-only.

UPDATE Statement Syntax

UPDATE TableName
SET Column1 = Value1, Column2 = Value2
WHERE Condition;

Parts:

• UPDATE -- Modify records
• SET -- Which columns to change
• WHERE -- Which rows to update (critical!)

⚠️ Important: WHERE clause is REQUIRED

Always use WHERE. Without it, UPDATE changes ALL rows in table.

-- WRONG -- updates EVERY student
UPDATE Student SET ClassName = '11-A';

-- RIGHT -- updates only student 1
UPDATE Student SET ClassName = '11-A' WHERE StudentId = 1;

Example 1: UPDATE Single Column

Update one student's class:

UPDATE Student
SET ClassName = '11-A'
WHERE StudentId = 1;

Before:

StudentId | Name       | ClassName
1         | Ravi Kumar | 10-A

After:

StudentId | Name       | ClassName
1         | Ravi Kumar | 11-A

Example 2: UPDATE Multiple Columns

Update student's class and section:

UPDATE Student
SET ClassName = '11-A', Section = 'B'
WHERE StudentId = 1;

Before:

StudentId | Name       | ClassName | Section
1         | Ravi Kumar | 10-A      | A

After:

StudentId | Name       | ClassName | Section
1         | Ravi Kumar | 11-A      | B

Example 3: UPDATE With Calculation

Increase all teacher salaries by 5%:

UPDATE Teacher
SET Salary = Salary * 1.05;

All teacher salaries multiply by 1.05 (5% increase).

Before:

TeacherId | Name      | Salary
1         | Dr. Mehta | 50000.0
2         | Mrs. Rao  | 55000.0

After:

TeacherId | Name      | Salary
1         | Dr. Mehta | 52500.0
2         | Mrs. Rao  | 57750.0

Example 4: UPDATE Multiple Records With WHERE

Mark all students in 10-A as graduated:

UPDATE Student
SET Status = 'Graduated'
WHERE ClassName = '10-A';

Updates multiple rows (all in class 10-A).

Before:

StudentId | Name         | ClassName | Status
1         | Ravi Kumar   | 10-A      | Active
2         | Priya Sharma | 10-A      | Active
3         | Arjun Reddy  | 10-B      | Active

After:

StudentId | Name         | ClassName | Status
1         | Ravi Kumar   | 10-A      | Graduated
2         | Priya Sharma | 10-A      | Graduated
3         | Arjun Reddy  | 10-B      | Active

DELETE Statement Syntax

DELETE FROM TableName
WHERE Condition;

Parts:

• DELETE FROM -- Remove records
• WHERE -- Which rows to delete (critical!)

⚠️ Critical: WHERE clause is REQUIRED for DELETE

Always use WHERE. Without it, DELETE removes ALL rows.

-- WRONG -- deletes EVERY student
DELETE FROM Student;

-- RIGHT -- deletes only student 1
DELETE FROM Student WHERE StudentId = 1;

Example 5: DELETE Single Record

Delete one student:

DELETE FROM Student WHERE StudentId = 1;

Before:

StudentId | Name       | ClassName
1         | Ravi Kumar | 10-A
2         | Priya Sharma | 10-A

After:

StudentId | Name         | ClassName
2         | Priya Sharma | 10-A

Example 6: DELETE Multiple Records

Delete all students with status = 'Graduated':

DELETE FROM Student WHERE Status = 'Graduated';

Removes multiple rows matching condition.

Example 7: DELETE With Complex WHERE

Delete inactive students in class 10-A:

DELETE FROM Student 
WHERE ClassName = '10-A' AND Status = 'Inactive';

Only deletes rows matching ALL conditions.

Checking Before UPDATE/DELETE

Always check what will be affected BEFORE updating/deleting:

-- First, see what will be updated
SELECT * FROM Student WHERE ClassName = '10-A';

-- If result looks correct, THEN update
UPDATE Student
SET Status = 'Graduated'
WHERE ClassName = '10-A';

Rule: Check with SELECT before UPDATE/DELETE.

---

Common UPDATE & DELETE Mistakes

Mistake 1: UPDATE without WHERE (updates entire table)

Wrong:

UPDATE Student SET ClassName = '11-A';
-- Updates ALL 10,000 students to 11-A!

Disaster. All students changed.

Result: Data corruption. All records identical.

Fix: Always use WHERE:

UPDATE Student 
SET ClassName = '11-A' 
WHERE StudentId = 1;

Mistake 2: DELETE without WHERE (deletes entire table)

Wrong:

DELETE FROM Student;
-- Deletes ALL students!

Disaster. Table empty.

Result: Data loss. Entire table gone.

Fix: Always use WHERE:

DELETE FROM Student WHERE StudentId = 1;

Mistake 3: Testing in production (accidental data loss)

Wrong:

-- "Quick test" directly on production database
DELETE FROM Student WHERE Status = 'Inactive';
-- Oops, deleted 500 students!

No backup. No undo.

Result: Data permanently lost.

Fix: Test on development database first:

1. Write query on DEV database
2. Test with SELECT first
3. Verify with WHERE clause
4. THEN run on production

Mistake 4: Forgetting to check related data

Wrong:

DELETE FROM Student WHERE StudentId = 1;
-- What about ExamResult records for this student?

May break foreign key constraints.

Result: Constraint violation or orphaned records.

Fix: Check related tables before deleting:

-- First check if student has exam results
SELECT * FROM ExamResult WHERE StudentId = 1;

-- Then decide: delete results first? Or cascade?
DELETE FROM Student WHERE StudentId = 1;

---

Best Practices for UPDATE & DELETE

1. Always use WHERE clause -- Never UPDATE/DELETE without it
2. Check with SELECT first -- See what will change before modifying
3. Test on dev database -- Never first-time on production
4. Use transactions -- ROLLBACK if something wrong
5. Backup before bulk operations -- Safety net for mistakes
6. Double-check WHERE conditions -- Especially with AND/OR
7. Check for foreign keys -- Delete might cascade or fail

---

🎯 Q1: What happens if I UPDATE or DELETE without WHERE?

UPDATE without WHERE:

UPDATE Student SET ClassName = '11-A';
-- Updates ALL students to 11-A

Entire table changed. Disaster.

DELETE without WHERE:

DELETE FROM Student;
-- Deletes ALL students

Entire table empty. Disaster.

This is why WHERE is critical. SQL Server will execute it, won't stop you.

Protection: Always write SELECT with same WHERE first:

SELECT * FROM Student WHERE StudentId = 1;  -- Check result
UPDATE Student SET ClassName = '11-A' WHERE StudentId = 1;  -- Same WHERE

🎯 Q2: Can I undo an UPDATE or DELETE?

No undo after COMMIT (in production).

During transaction (before COMMIT):

BEGIN TRANSACTION;
DELETE FROM Student WHERE StudentId = 1;
-- Oops, wrong student!
ROLLBACK;  -- Undo the delete

ROLLBACK undoes changes before COMMIT.

After COMMIT: Changes permanent (unless backed up).

Only recovery: Restore from backup.

Best practice: Use transactions for important operations:

BEGIN TRANSACTION;
UPDATE Student SET ClassName = '11-A' WHERE StudentId = 1;
-- Check result
COMMIT;  -- Make permanent

🎯 Q3: How do I update based on values from another table?

UPDATE with JOIN or subquery:

-- Update students whose teacher changed
UPDATE Student
SET Status = 'Updated'
WHERE TeacherId IN (SELECT TeacherId FROM Teacher WHERE Salary > 50000);

Updates students whose teacher salary > 50000.

Or with JOIN:

UPDATE s
SET s.Status = 'Updated'
FROM Student s
JOIN Teacher t ON s.TeacherId = t.TeacherId
WHERE t.Salary > 50000;

Both work. JOIN is clearer.

🎯 Q4: What's the difference between DELETE and TRUNCATE?

DELETE:

DELETE FROM Student WHERE StudentId > 100;

• Removes specific rows
• Slow for large tables
• Triggers triggers
• Can ROLLBACK

TRUNCATE:

TRUNCATE TABLE Student;

• Removes ALL rows (no WHERE)
• Fast for large tables
• Doesn't trigger triggers
• Can ROLLBACK (in transaction)
• Resets IDENTITY counter

Use:

• DELETE: Remove specific rows or small amounts
• TRUNCATE: Remove all data from table

🎯 Q5: How do I safely delete without losing data?

Step-by-step safe delete:

1. Check what will be deleted:

   SELECT * FROM Student WHERE Status = 'Inactive';

2. Backup table (optional but recommended):

   SELECT * INTO Student_Backup FROM Student;

3. Use transaction:

   BEGIN TRANSACTION;
   DELETE FROM Student WHERE Status = 'Inactive';
   -- Check deletion
   ROLLBACK;  -- Undo if wrong
   -- Or COMMIT; -- Make permanent if correct

4. Verify results:

   SELECT COUNT(*) FROM Student;  -- Check count

Never: DELETE directly on production without these steps.

🎯 Q6: What's the risk of UPDATE/DELETE on large tables?

Performance issue:

UPDATE Student SET Status = 'Active';  -- All 1M students
-- Locks table, slow, blocks other queries

Table locked during update. Other users wait. API times out.

Better approach -- batch updates:

-- Update 1000 at a time
WHILE (SELECT COUNT(*) FROM Student WHERE Status = NULL) > 0
BEGIN
    UPDATE TOP(1000) Student 
    SET Status = 'Active' 
    WHERE Status = NULL;
    COMMIT;
END

Smaller transactions, shorter locks, other queries can run.

Rule: Large operations can lock tables. Test on dev first. Run off-peak.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on UPDATE and DELETE statements in SQL Server. Try these prompts:

• "Explain UPDATE and DELETE syntax step-by-step with examples"
• "Why is WHERE clause critical for UPDATE and DELETE?"
• "How do I safely delete data without losing everything?"
• "What's the difference between DELETE and TRUNCATE?"
• "Quiz me: ask 5 questions about UPDATE, DELETE, WHERE, and safe data modification"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

08. Data Types -- Complete Guide ->