SQL - UPDATE JOIN

---

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

For instance, if a student changes their primary phone number and wishes to update it in their organizational database, the information needs to be modified in multiple tables like student records, laboratory records, canteen passes etc. Using UPDATE… JOIN statement, you can combine all these tables into one, and then update the student data in them.

Let us learn more about this statement further in this chapter.

UPDATE… JOIN in SQL

Usually, JOINS in SQL 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.

To get a better idea, let us approach this concept in another way. We know that when a SELECT statement is used with JOINS, it displays the contents of multiple tables; similarly, when an UPDATE statement is used with JOINS, it updates the contents of multiple tables.

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;

When we say JOIN here, we can use any type of Join: Regular Join, Natural Join, Inner Join, Outer Join, Left Join, Right Join, Full Join etc.

Example

Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary etc., using the following query −

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 (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

The 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 | MP        |  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 (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102, '2009-10-08 00:00:00', 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100, '2009-10-08 00:00:00', 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101, '2009-11-20 00:00:00', 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103, '2008-05-20 00:00:00', 4, 2060.00);

The 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 |
+-----+---------------------+-------------+---------+

Update Join Query

Use the following UPDATE… JOIN query to cross-modify multiple tables −

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

Output

The output is displayed as follows −

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement.

Following is the query to display the records in the Customers table.

SELECT * FROM Customers;

The updated table is displayed −

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

You must remember that with the query above, we only tried to update the Customers table, hence, changes will not be reflected in the Orders table.

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.

Syntax

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
SET SALARY = SALARY + 1000
FROM CUSTOMERS 
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE CUSTOMERS.SALARY = 2000.00;

Output

The output is displayed as −

(1 row affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement.

Following is the query to display the records in the Customers table.

SELECT * FROM Customers;

The updated table is displayed −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  3000.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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Only Customers table is updated using the query above, hence, the Orders table remains unchanged.