MySQL UPDATE Statement

🔄 MySQL UPDATE Statement

The UPDATE statement in MySQL is used to modify existing records in a table.

⚠️ Be careful: Without a WHERE clause, all rows will be updated.

1️⃣ What is UPDATE?

• Modifies existing data

• Can update one or multiple columns

• Works with WHERE, ORDER BY, LIMIT, and JOIN

2️⃣ Basic Syntax

3️⃣ Simple Example ⭐

Update a Single Column

4️⃣ Update Multiple Columns

5️⃣ UPDATE Without WHERE ❌ (Dangerous)

❌ Updates all rows in the table

📌 Always check with SELECT first:

6️⃣ UPDATE with WHERE Conditions

7️⃣ UPDATE with LIMIT ⭐ (MySQL Feature)

✔ Updates lowest 3 marks

8️⃣ UPDATE with ORDER BY

9️⃣ UPDATE with JOIN ⭐ (Very Important)

Update one table using data from another table.

🔟 UPDATE with Subquery

1️⃣1️⃣ UPDATE with CASE ⭐

1️⃣2️⃣ UPDATE vs INSERT vs DELETE ⭐ (Interview)

1️⃣3️⃣ Common Mistakes ❌

❌ Forgetting WHERE clause
❌ Wrong condition (updates wrong rows)
❌ Not checking affected rows
❌ Updating primary/foreign keys blindly

📌 Check affected rows:

📌 Interview Questions & MCQs (Very Important)

Q1. What does UPDATE do?

A) Insert rows
B) Modify rows
C) Delete rows
D) Select rows

✅ Answer: B

Q2. What happens if WHERE clause is omitted?

A) Error
B) Only one row updated
C) All rows updated
D) No rows updated

✅ Answer: C

Q3. Can UPDATE modify multiple columns?

A) Yes
B) No

✅ Answer: A

Q4. Can UPDATE be used with JOIN in MySQL?

A) Yes
B) No

✅ Answer: A

Q5. Which clause limits number of updated rows?

A) GROUP BY
B) HAVING
C) LIMIT
D) OFFSET

✅ Answer: C

Q6. Which is safest practice before UPDATE?

A) DROP table
B) SELECT with same WHERE
C) COMMIT
D) CREATE INDEX

✅ Answer: B

🔥 Real-Life Use Cases

✔ Salary revision
✔ Status updates
✔ Data correction
✔ Bulk changes
✔ Conditional updates

✅ Summary

• UPDATE modifies existing data

• Use WHERE to avoid full-table updates

• Supports JOIN, CASE, LIMIT

• Very powerful but risky if misused

• Crucial for SQL exams & interviews