How to Update in MySQL

Updating data in MySQL is a fundamental skill for managing your database effectively. This tutorial will show you how to use the UPDATE statement to modify existing records.

Basic Syntax

The basic syntax of the UPDATE statement is:

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

Important: Always use a WHERE clause to avoid accidentally updating all records in the table.

Example 1: Update a Single Record

UPDATE employees
SET salary = 60000
WHERE employee_id = 1;

This command updates the salary of the employee with employee_id = 1 to 60,000.

Example 2: Update Multiple Columns

UPDATE employees
SET salary = 65000, department = 'Engineering'
WHERE employee_id = 2;

Here, we update both salary and department for the employee with employee_id = 2.

Example 3: Update Multiple Rows

UPDATE employees
SET department = 'Sales'
WHERE department = 'Marketing';

This updates the department field from Marketing to Sales for all relevant employees.

Best Practices

• ✅ Always use a WHERE clause unless you intend to update all rows.
• ✅ Make a backup before running large update queries.
• ✅ Test your query with a SELECT statement first to check which rows will be affected.
• ✅ Use transactions (START TRANSACTION, COMMIT, ROLLBACK) when making multiple updates.

Using Transactions (Advanced)

START TRANSACTION;

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Engineering';

COMMIT;

This increases salaries in the Engineering department by 10% within a transaction, ensuring all updates succeed or none are applied.

Conclusion

With the UPDATE statement, you can keep your MySQL database current and accurate. Remember to use caution and follow best practices to avoid data loss or unintended changes.