mysql
  1. mysql-update-join
PreviousNext

Update Join - (MySQL Join)

In MySQL, you can use a JOIN statement to combine rows from two or more tables. When combined with an UPDATE statement, you can use a JOIN to update rows in one table based on the values in another.

Syntax

The basic syntax for an UPDATE JOIN statement in MySQL is as follows:

UPDATE table1
JOIN table2 ON join_condition
SET table1.column = value
WHERE condition;

The UPDATE JOIN statement updates the rows in table1 that match the given WHERE condition, using the values from table2 that match the JOIN condition.

Example

Suppose we have two tables, employees and salaries, and we want to update all employees whose salary is less than $50,000 to have a salary of $50,000.

UPDATE employees
JOIN salaries ON employees.id = salaries.employee_id
SET salaries.salary = 50000
WHERE salaries.salary < 50000;

In this example, we are joining the employees and salaries tables on the id and employee_id columns, respectively. We are then setting the salary column in the salaries table to 50000 for all rows where the salary is less than 50000.

Output

The output of the UPDATE JOIN statement is the number of rows that were updated in table1.

Explanation

An UPDATE JOIN statement in MySQL allows us to update rows in one table based on the values in another table using a JOIN. In the example above, we are joining the employees and salaries tables on the id and employee_id columns, respectively. We then set the salary column in the salaries table to 50000 for all rows where the salary is less than 50000.

Use

An UPDATE JOIN statement can be useful in cases where you need to update multiple tables at once based on a common condition.

Important Points

  • The JOIN condition in the UPDATE JOIN statement is used to match rows between the tables being joined.
  • The SET clause is used to update the columns in table1.
  • The WHERE clause is used to filter the rows that will be updated.

Summary

An UPDATE JOIN statement in MySQL allows us to update rows in one table based on the values in another table. This is done using a JOIN to match rows between the two tables. The UPDATE JOIN statement is useful when you need to update multiple tables at once based on a common condition.

DELETE JOINEquiJoin
Published on: