Update Query using joins in MySql 


In this Article, I will show how to write Mysql update query using joins.You can use multiple tables in your single Update query.

Example :



Departments Table :
id Name
1 Accounts
2 R&D
3 Sales


Employees Table :
id Name Salary Department Id
1 Rajesh 50000 2
2 Sachin 80000 2
3 Rahul 20000 1

MySql Query to update employee record whose salary is greater than 50000 and department name is R&D.


Update employees 
inner join departments ON empoyees.department_id=departments.id
SET employees.salary=40000
WHERE  employees.salary>50000 and department.name='R&D'



MySql Query to delete employee record whose salary is greater than 50000 and department name is R&D.


DELETE employees 
inner join departments ON empoyees.department_id=departments.id
WHERE employees.salary>50000 and department.name='R&D'


Updating Query Using Joins in PHP Script:

<?php
$host = 'localhost:3036';
$user = 'root';
$pass = 'rootpassword';
$conn = mysql_connect($host, $user, $pass);
mysql_select_db('sampledb');

 $sql = "Update employees 
join departments ON empoyees.department_id=departments.id
SET employees.salary=40000
WHERE  employees.salary>50000 and department.name='R&D'";

 $result = mysql_query( $sql, $conn );
echo "data updated successfully\n";
mysql_close($conn);
?>