Course
Update Join
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Update Join
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 −
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 −
Use the following UPDATE... JOIN query to cross-modify multiple tables (CUSTOMERS and ORDERS) −
UPDATE CUSTOMERSJOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_IDSET 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 −
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 = column4WHERE 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 −
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
PHPNodeJSJavaPython
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);
Example
Following are the programs −
PHPNodeJSJavaPython
$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: 101Author: Sanjay, Count: 101