Write an Article?
Ask Question

MySQL Update Statement

Previous Page
Next Page

The MySQL Update statement is used to updated or modify an existing record or records in a table. The Update statement is one of the DML statements that MySQL supports.

MySQL Update syntax

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

We will use the MySQL Workbench for these examples. The procedure for the MySQL Command-line client is the same.

MySQL Update statement to update single record from a table

If we observe, the record from emp_id=27 does not have a last_name which is incorrect. To update the record, we use the syntax.

UPDATE EMPLOYEES
SET LAST_NAME = 'Mirkwood'
WHERE EMP_ID=27;

IF we do not use the where clause, all the records in the table will be updated.

MySQL Update statement with multiple column values from a table

We can also update multiple columns from a table at once. To update the record(s), we use the syntax.

UPDATE EMPLOYEES
SET 
LAST_NAME = 'Firebeams',
BIRTH_DATE = '2785-02-01'
WHERE LAST_NAME IS NULL;

The update statement above will update the records for emp_id = 39 and emp_id = 40 with the same values for the Last_name and the birth_Date field.

MySQL Update statement with Replace

We can use the replace statement with the update statement to update a particular string with another, which could affect multiple rows or a single row. The Query is:

UPDATE DEPARTMENTS
SET DEPT_NAME = REPLACE(dept_name,'Rnd','Research and Development')
WHERE DEPT_ID = 5;

MySQL Update statement using the select statement as a subquery

We can select multiple records in a single update statement using the select subquery.

UPDATE EMPLOYEES
SET 
EMAIL = '[email protected]'
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_DEPT WHERE DEPT_ID = 2);

The query would select all the records for department =2 and update them with a generic email, i.e., [email protected].

MySQL Update statement with the select statement in the set clause

Consider that we have to change the department id from its current value to the department's value at the "Ravenhill" location for a specific record. We can do so by using the select statement in the set clause. The query is as follows.

UPDATE EMPLOYEE_DEPT
SET
DEPT_ID = (SELECT DEPT_ID FROM DEPT_LOCATIONS WHERE LOCATION_NAME = 'Ravenhill')
WHERE RECORD_ID=15;

The output of the same is as follows.

MySQL Update statement with INNER JOIN

Based on the rating each employee gets, we update their salary in the employee's table. The query applies only to the Support department. For all other departments, all employees get a standard 40% hike in salary. 

UPDATE EMPLOYEES
INNER JOIN
RATINGS ON EMPLOYEES.RATING = RATINGS.RATING_ID 
SET 
SALARY = ROUND(SALARY + SALARY * PERCENTAGE ,0)
WHERE RATING <> 0;

Previous Page
Next Page