MySQL UPDATE Statement

The UPDATE statement modifies existing data in a table. Instead of deleting a row and inserting a new one, UPDATE changes one or more column values in rows that match a specified condition.

Syntax

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

• SET specifies which columns to change and what new values to assign.
• WHERE identifies which rows to update. Without WHERE, every row in the table is updated.

Sample Table: employees

+--------+--------+------------+--------+
| emp_id | name   | department | salary |
+--------+--------+------------+--------+
|      1 | Alice  | IT         |  60000 |
|      2 | Bob    | HR         |  55000 |
|      3 | Carol  | IT         |  62000 |
|      4 | David  | Finance    |  70000 |
+--------+--------+------------+--------+

Updating a Single Column

Give Bob a salary raise to 58000:

UPDATE employees
SET salary = 58000
WHERE emp_id = 2;

Only the row where emp_id = 2 (Bob) is changed. All other rows remain the same.

Updating Multiple Columns at Once

Transfer Carol from IT to Finance and update her salary:

UPDATE employees
SET department = 'Finance', salary = 65000
WHERE emp_id = 3;

Both the department and salary columns are updated for Carol in a single statement.

Updating Multiple Rows with One Condition

Give all IT department employees a 5000 raise:

UPDATE employees
SET salary = salary + 5000
WHERE department = 'IT';

This updates every employee in the IT department. The expression salary + 5000 uses the existing salary value and adds 5000 to it.

Verifying the Update

Check the changes after an update:

SELECT * FROM employees
WHERE department = 'IT';

Updating Without a WHERE Clause (Caution)

Omitting the WHERE clause updates every row in the table:

-- WARNING: This updates ALL employees' salary to 50000
UPDATE employees
SET salary = 50000;

This is almost never the intended behavior and can cause irreversible data loss if not carefully considered. Always double-check the WHERE clause before running an UPDATE.

Safe Update Mode

MySQL has a "safe update" mode that prevents updates and deletes without a WHERE clause that includes a key column. If this mode is enabled and an unsafe update is run, MySQL blocks it. This is a useful safeguard in production environments.

-- Enable safe update mode
SET SQL_SAFE_UPDATES = 1;

-- Disable safe update mode (use with care)
SET SQL_SAFE_UPDATES = 0;

Using UPDATE with JOIN (Preview)

It is also possible to update one table based on values in another table using a join. This is covered in detail in the JOIN topics.

Complete Example

-- Before update
SELECT * FROM employees WHERE emp_id = 1;

-- Apply the update
UPDATE employees
SET salary = 65000, department = 'Management'
WHERE emp_id = 1;

-- Verify the change
SELECT * FROM employees WHERE emp_id = 1;

Key Points

• UPDATE modifies existing rows in a table.
• The SET clause defines which columns to update and their new values.
• Always include a WHERE clause to target specific rows — omitting it updates every row.
• Multiple columns can be updated in a single statement by separating assignments with commas.
• Expressions like salary = salary + 5000 can be used to calculate new values based on existing ones.