MySQL - Update Join

Table of content

To update the data entered in a single database table using MySQL, you can use the UPDATE statement. However, to update the data in multiple database tables, we use the UPDATE... JOIN statement.

MySQL UPDATE... JOIN

Usually, JOINS in MySQL are used to fetch the combination of rows from multiple tables, with respect to a matching field. And since the UPDATE statement only modifies the data in a single table, we combine multiple tables into one using JOINS and then update them. This is also known as cross-table modification.

Syntax

Following is the basic syntax of the UPDATE... JOIN statement −

UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1 
JOIN table2 ON column3 = column4;

Example

Let us first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The CUSTOMERS table will be created as −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The ORDERS table is displayed as follows −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Use the following UPDATE... JOIN query to cross-modify multiple tables (CUSTOMERS and ORDERS) −

UPDATE CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000;

Verification

As we can see in the CUSTOMERS table below, the changes we have performed in the above query are reflected −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 2500.00
3 Kaushik 23 Kota 3000.00
4 Chaitali 25 Mumbai 7500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

UPDATE... JOIN with WHERE Clause

The ON clause in UPDATE... JOIN query is used to apply constraints on the records to be updated. In addition to it, we can also use WHERE clause to make the constraints stricter. The syntax of it is as follows −

UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1 
JOIN table2 ON column3 = column4
WHERE condition;

Example

Observe the query below. Here, we are trying to increase the salary of CUSTOMERS who only earn 2000.00 −

UPDATE CUSTOMERS 
LEFT JOIN ORDERS 
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
SET SALARY = SALARY + 1000 
WHERE CUSTOMERS.SALARY = 2000.00;

Verification

As we can see in the CUSTOMERS table below, the CUSTOMERS who are earning the salary of 2000 got an increment of 1000 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 3000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 3000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Update Join Using Client Program

In addition to join two or more tables using the MySQL query, we can also perform the update join operation using a client program.

Syntax

PHP NodeJS Java Python

To perform Update Join through a PHP program, we need to execute the UPDATE statement with JOIN clause using the mysqli function query() as follows −

$sql = 'UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author 
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100';
$mysqli->query($sql);

To perform Update Join through a JavaScript program, we need to execute the UPDATE statement with JOIN clause using the query() function of mysql2 library as follows −

sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";
con.query(sql);

To perform Update Join through a Java program, we need to execute the UPDATE statement with JOIN clause using the JDBC function executeUpdate() as follows −

String sql = "UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author 
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100";
statement.executeUpdate(sql);

To perform Update Join through a Python program, we need to execute the UPDATE statement with JOIN clause using the execute() function of the MySQL Connector/Python as follows −

update_join_query = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"
cursorObj.execute(update_join_query)

Example

Following are the programs −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}    
// Updating Join
$sql = 'UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100';
if ($mysqli->query($sql)) {
    echo "Join updated successfully! \n";
} else {
    echo "Join could not be updated! \n";
}

// Selecting the updated value
$update_res = 'SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author';

$result = $mysqli->query($update_res);

if ($result->num_rows > 0) {
    echo "Updated tutorial_count! \n";
    while ($row = $result->fetch_assoc()) {
        printf("Author: %s, Count: %d", $row["tutorial_author"], $row["tutorial_count"]);
        printf("
");
    }
} else {
    printf('No record found.
');
}
mysqli_free_result($result);    
$mysqli->close();

Output

The output obtained is as follows −

Join updated successfully!
Updated tutorial_count!
Author: John Paul, Count: 101
Author: Sanjay, Count: 101   

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //UPDATE JOIN
  sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";

  //displaying the updated data along with ID;
  sql =
    "SELECT c.ID, c.SALARY FROM Customers c INNER JOIN Orders o  ON c.ID = o.CUSTOMER_ID";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[
  { ID: 3, SALARY: '5000.00' },
  { ID: 3, SALARY: '5000.00' },
  { ID: 2, SALARY: '3060.00' },
  { ID: 4, SALARY: '8560.00' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class UpdateJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //MySQL Update JOIN...!;
         String sql = "UPDATE tcount_tbl\n" + 
         "INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author\n" + 
         "SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100\n";
         statement.executeUpdate(sql);
         System.out.println("tutorial_count updated successfully...!");

         //fetch the records...!;
         ResultSet resultSet = statement.executeQuery("SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getInt(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
tutorial_count updated successfully...!
John Paul 201
Sanjay 201

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
update_join_query = f"""UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"""
cursorObj.execute(update_join_query)
connection.commit()
print("updated successfully")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

updated successfully
Advertisements