Updating Records (UPDATE)

Once you’ve inserted data into your tables, there may be times when you need to change some of that data — whether correcting a typo or updating student marks. That’s where the UPDATE statement comes in.

🎯 What You’ll Learn

• Syntax and structure of the UPDATE command
• How to use WHERE with UPDATE to target specific rows
• How to update multiple columns at once
• Safety tips to prevent unwanted changes

🔧 Basic Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition ;  

⚠️Always use the WHERE clause! If you forget it, all rows in the table will be updated!

🧪 Let’s Work With Our students Table

id	name	age	course	marks
1	Rahul Sharma	21	SQL	85.50
2	Anjali Mehta	22	SQL	91.75
3	Vikas Gupta	23	SQL	88.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	SQL	76.00

🛠️ Example 1: Update Marks for One Student

Let's say Rahul Sharma scored 90 now. Update his marks:

UPDATE students SET marks = 90 WHERE id = 1 ;  

✅ Output (after update):

id	name	marks
1	Rahul Sharma	90.00

🛠️ Example 2: Update Course Name for Multiple Students

Suppose we want to change course from 'SQL' to 'Advanced SQL' :

UPDATE students SET course = 'Advanced SQL' WHERE course = 'SQL' ;  

✅ Output (after update):

id	name	course
1	Rahul Sharma	Advanced SQL
2	Anjali Mehta	Advanced SQL
3	Vikas Gupta	Advanced SQL
5	Aman Verma	Advanced SQL

🛠️ Example 3: Update Multiple Columns Together

Let’s change Vikas Gupta’s age and marks:

UPDATE students SET age = 24 , marks = 91 WHERE name = 'Vikas Gupta' ;  

✅ Output (after update):

name	age	marks
Vikas Gupta	24	91.00

🧠 Pro Tips

Tip	Description
Always use WHERE clause	Avoid updating every row by mistake
Use LIMIT (MySQL) in testing	Helps restrict updates for debugging
Use TRANSACTION (advanced)	Helps undo changes if something goes wrong
Test with SELECT first	Run the same WHERE clause with a SELECT first

🔍 Real-World Use Case

A student changed her name legally:

UPDATE students SET name = 'Anjali Kapoor' WHERE name = 'Anjali Mehta' ;  

⚠️ Common Mistakes

Mistake	Why it's bad
Forgetting WHERE clause	Updates every row!
Incorrect condition in WHERE	Nothing or wrong rows get updated
Using wrong column names	Leads to syntax errors or failed execution

📌 Summary

• UPDATE modifies existing records.
• Always use WHERE to update only what you intend.
• You can update one or many columns at a time.
• Test your conditions with a SELECT before running the actual update.