MySQL – Update Join

MySQL Update Join is a statement that performs a cross-table update by including JOIN clauses in the UPDATE query command. A MySQL Update Join is used to execute an update statement, as well as to implement INNER JOIN and LEFT JOIN MySQL clauses. Using this Update JOIN clause in MySQL, you can retrieve data records from related database tables and modify them using a query. The query process produces a cross-table update by joining and altering one database table using another, based on the PRIMARY and FOREIGN Keys defined on the tables and the join condition in the database server.

Syntax

UPDATE Table1, Table2, [LEFT JOIN clause| INNER JOIN clause] Table1 ON Table1.ColA = Table2.ColA
SET Table1.ColB = Table2.ColB, Table2.ColC = Expression
WHERE Condition

Let’s first take a look at the main table (Table1) and another table (Table2) used to join the main table after the UPDATE query. The UPDATE clause can only be executed if at least one table is identified; otherwise, the rows in the table will not be updated.

As a second step, we need to state the JOIN clause right after the clause UPDATE. The type of JOIN you wish to apply can be selected from the two described above, i.e., INNER JOIN or LEFT JOIN, along with the predicate of the join.

The next step will be to set the new values and assign them to both table columns, Table 1 and Table 2, that need to be modified. This expression specifies any expressional values or column fields that must be included in the update clause. A WHERE clause can also be used as a joining condition to limit the records to be updated.

Example:

UPDATE dept_emp INNER JOIN employees ON dept_emp.emp_no = employees.emp_no SET Salary = Salary + Salary ;