MySQL Update Multiple Rows

Update multiple rows in MySQL is an important Data Manipulation Language (DML) Command in MySQL. Its uses for updating multiple existing records in the table.

Update multiple rows Syntax

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET column_name1=('new_value','new_value2','new_value3',..),
column_name2=('new_value','new_value2','new_value3',..),
.................
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

1. After UPDATE Need to follow Table Name Ex: Employee.
2. SET clause: In the SET clause, we need to specify multiple lists of columns that we need to change and assign multiple new values with ‘,’ comma seperation.
3. Where clause: where the condition is useful for specifying which records need to update. If you forgot WHERE condition then it will update all records.
4. Even you can specify ORDER BY and LIMIT in the MySQL update command. But if you are updating all records don’t specify ORDER BY for best practice.
5. You can use LOW-PRIORITY, it will wait until other high priority threads that are using the same resource.
6. If you use IGNORE then it will not return an error in case of Primary key or UNIQUE key violation.

Update Multiple columns Example

UPDATE emp.employee e
 SET
   e.email = ('email1','email2','email3'),
   e.dep_no = ('dep_no1',dep_no2,dep_no3)
 WHERE
   e.emp_no = 'emp_no';

SmartMySQL Workbench has advance options for writing UPDATE command and you no need to remember UPDATE command syntax.

If you Type U or UPDATE then drag table after U letter or UPDATE Keyword then it generates UPDATE COMMAND for that table. Ex: if you type U then drag the “Employee” table then it generates the following UPDATE command.

UPDATE `emp`.`employee` e
SET
  e.email = 'email',
  e.dep_no = 'dep_no'
WHERE
  e.emp_no = 'emp_no';

Default it will get all columns where SET Clause. You can keep which column you need rest you can remove. By default, it will generate where condition with the primary key column. If you need other columns in WHERE clause then you need to drag the column after U letter.

For example, if I want an email column in the WHERE clause then I need to select an email column then drag it after the U keyword in the query browser.

UPDATE `emp`.`employee` e
SET
  e.emp_no = 'emp_no',
  e.birth_date = 'birth_date',
  e.first_name = 'first_name',
  e.last_name = 'last_name',
  e.gender = 'gender',
  e.hire_date = 'hire_date',
  e.email = 'email',
  e.dep_no = 'dep_no',
  e.salary = 'salary'
WHERE
  e.email = '';

Default the SET clause return all columns and you can keep which column you need and remove the rest of the columns. If you drag a new column in the SET clause or the WHERE clause then those columns will be added into query

MySQL UPDATE with JOIN

You can simply drag a table into the existing UPDATE statement then it generates UPDATE query with the table with joins condition. Ex: if I drag Department table then it generates the following UPDATE command with JOIN condition.

UPDATE employee e
  INNER JOIN departments d
    ON (d.dept_no = e.dep_no)
SET
  e.emp_no = 'emp_no',
  e.birth_date = 'birth_date',
  e.first_name = 'first_name',
  e.last_name = 'last_name',
  e.gender = 'gender',
  e.hire_date = 'hire_date',
  e.email = 'email',
  e.dep_no = 'dep_no',
  e.salary = 'salary'
WHERE
  e.email = '';

This option is available in SmartMySQL free version and if you use SmartMySQL you no need to remember any syntax. You just remove an unwanted column in the SET clause and fill values in the WHERE condition.

Ex: If I want to increase salary 10% for the Marketing department then I just keep the salary column in the SET clause in the above column and add dept_name in where clause and fill filter condition like dept_name=’Marketing’.

UPDATE employee e
  INNER JOIN departments d
    ON (d.dept_no = e.dep_no)
SET
  e.salary = salary*110/100
WHERE
  dept_name='Marketing';

Please let us know there are any other ideas to simply UPDATE query construction, we will consider your ideas and give rewards for your ideas.

The SmartMySQL Workbench is free and you can download. If any problem contacts support@smartmysql.com.