Updating Existing Data

Hey there! 👋 Today, we’re going to talk about updating existing data in MySQL using the UPDATE statement. Imagine you have a notebook 📒 where you wrote down your friend’s phone number, but now they have a new one. Instead of erasing the old number and writing a new one, you just update it. That’s exactly what we do in a database with UPDATE!

Let’s dive in! 🚀

📌 What is the UPDATE Statement?

The UPDATE statement in MySQL allows you to modify existing records in a table. It helps in changing values without deleting or inserting new rows.

📝 Syntax

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

💡 Key Points to Remember

1. The UPDATE statement modifies existing data in a table.
2. The SET keyword defines which columns should be updated and what new values should be assigned.
3. The WHERE clause is important because it tells MySQL which rows to update. If you forget it, all rows in the table will be updated! 😱

🎯 Example 1: Updating a Single Record

🔹 Scenario

Let’s say we have a table called students, which stores information about students.

📌 Table: students

Now, John just turned 15. Let’s update his age in the database.

📝 Query

UPDATE students  
SET age = 15  
WHERE id = 1;

🔍 Output

✅ Now John’s age is updated in the table.

🎯 Example 2: Updating Multiple Columns

🔹 Scenario

Alice just got promoted to grade 10, and she also had her birthday. We need to update both age and grade.

📝 Query

UPDATE students  
SET age = 16, grade = 10  
WHERE id = 2;

🔍 Output

✅ Alice’s age and grade are updated successfully!

🎯 Example 3: Updating Multiple Rows

🔹 Scenario

All students in grade 8 are moving to grade 9.

📝 Query

UPDATE students  
SET grade = 9  
WHERE grade = 8;

🔍 Output

✅ Now, all students in grade 8 have been promoted to grade 9.

⚠️ Important: Be Careful with UPDATE!

If you forget the WHERE clause, you update every row in the table! 😱

❌ Example of a Dangerous Update

UPDATE students  
SET grade = 12;

🚨 This will make every student on the table move to grade 12! Always use the WHERE clause carefully.

🛠️ Practice Questions

Test your understanding with these exercises. Try writing the SQL queries on your own!

🔹 Question 1:

The employees table has the following data:

Write an SQL query to increase Alex’s salary to 60000.

💡 Expected Output:

🔹 Question 2:

The products table contains:

Write a query to increase the price of all products by 10%.

💡 Expected Output:

🎤 Interview Questions with Answers

1️⃣ What is the UPDATE statement in SQL?

Answer:
The UPDATE statement in SQL is used to modify existing records in a table. It updates one or more columns of a row or multiple rows based on a specified condition.

2️⃣ How can you update multiple columns in a table?

Answer:
To update multiple columns, separate each column assignment with a comma.
Example:

UPDATE employees  
SET salary = 60000, department = 'HR'  
WHERE emp_id = 101;

3️⃣ What happens if you forget the WHERE clause in an UPDATE statement?

Answer:
If you forget the WHERE clause, every row in the table will be updated, which can cause serious data issues.

4️⃣ Can we update multiple rows in one query?

Answer:
Yes, we can update multiple rows using a condition in the WHERE clause.
Example:

UPDATE students  
SET grade = 10  
WHERE grade = 9;

5️⃣ How do you increase a numeric value in a column?

Answer:
You can use arithmetic operations like this:

UPDATE products  
SET price = price * 1.10;

This increases the price by 10%.

🎯 Summary

• The UPDATE statement modifies existing records.
• Use SET to define the new values.
• Always use the WHERE clause to avoid unwanted updates.
• You can update single or multiple rows at once.
• Be cautious when updating data to prevent accidental changes.

🎯 Final Challenge

Try writing an SQL query to:

• Reduce the price of all books in a library table by 5%.
• Change the department of all employees in “Sales” to “Marketing” in an employees table.