UPDATE in MySQL

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

1. Basic Syntax

UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;

🔹 Important Notes:
✅ Always use WHERE to avoid updating all records.
✅ You can update multiple columns at once.

2. Example Table: employees

3. Updating a Single Column

UPDATE employeesSET salary = 6500WHERE emp_id = 1;

✅ Changes Alice’s salary to 6500.

4. Updating Multiple Columns

UPDATE employeesSET department = 'Finance', salary = 7000WHERE emp_id = 2;

✅ Changes Bob's department to "Finance" and salary to 7000.

5. Updating Multiple Rows

UPDATE employeesSET salary = salary + 500WHERE department = 'IT';

✅ Increases salary by 500 for all IT employees.

6. Updating All Rows (Use with Caution ⚠️)

UPDATE employeesSET salary = 6000;

✅ Changes salary for ALL employees! (Use WHERE to avoid this.)

7. Using ORDER BY & LIMIT

UPDATE employeesSET salary = 7500ORDER BY salary ASCLIMIT 1;

✅ Updates only the employee with the lowest salary.

8. Using UPDATE with JOIN

UPDATE employees eJOIN departments d ON e.department = d.dept_nameSET e.salary = e.salary + 1000WHERE d.dept_id = 2;

✅ Increases salary by 1000 for employees in department ID 2.

9. Checking Changes with SELECT

After running an UPDATE, you can verify the changes using:

SELECT * FROM employees;

10. Using Transactions (To Undo Changes)

START TRANSACTION;UPDATE employees SET salary = 8000 WHERE emp_id = 3;ROLLBACK; -- Undo the changeCOMMIT; -- Save the change

✅ Use ROLLBACK to undo an accidental update.

Key Takeaways

✅ Use WHERE to avoid updating all records.
✅ You can update one or multiple columns at once.
✅ Use ORDER BY & LIMIT to control updates.
✅ Use JOIN for updating data from multiple tables.
✅ Use START TRANSACTION and ROLLBACK to prevent mistakes.