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 need to use the UPDATE… JOIN clause.
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 the JOIN clause, you can combine all these tables into one, and then using UPDATE statement, you can update the student data in them simultaneously.
The SQL UPDATE… JOIN Clause
The UPDATE statement only modifies the data in a single table and JOINS in SQL are used to fetch the combination of rows from multiple tables, with respect to a matching field.
If we want to update data in multiple tables, we can combine multiple tables into one using JOINS and then update them using UPDATE statement. This is also known as cross-table modification.
Syntax
Following is the basic syntax of the SQL UPDATE… JOIN statement −
UPDATE table(s) JOIN table2 ON table1.join_column = table2.join_column SET table1.column1 = table2.new_value1, table1.column2 = table2.new_value2;
Where, JOIN can be: 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 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 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 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 |
Following UPDATE… JOIN query increments the salary of customers by 1000 with respect to the inflation of their order amount by 500 −
UPDATE CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000, ORDERS.AMOUNT = ORDERS.AMOUNT + 500;
Verification
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows −
SELECT * FROM CUSTOMERS;
The updated CUSTOMERS table is displayed as follows −
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 |
Now, check whether the ORDERS table is updated using the following SELECT statement −
SELECT * FROM ORDERS;
The updated ORDERS table is displayed as follows −
OID | DATE | CUSTOMER_ID | AMOUNT |
---|---|---|---|
102 | 2009-10-08 00:00:00 | 3 | 3500.00 |
100 | 2009-10-08 00:00:00 | 3 | 2000.00 |
101 | 2009-11-20 00:00:00 | 2 | 2060.00 |
103 | 2008-05-20 00:00:00 | 4 | 2560.00 |
UPDATE… JOIN with WHERE Clause
While updating records from multiple tables, if we use the WHERE clause along with the UPDATE… JOIN statement we can filter the records to be updated (from the combined result set).
Syntax
The syntax of SQL UPDATE… JOIN with WHERE clause in MySQL database is as follows −
UPDATE table(s) JOIN table2 ON column3 = column4 SET table1.column1 = value1, table1.column2 = value2, ... WHERE condition;
Example
Now, let us execute the following query to increase the salary of customer whose id is 3 −
UPDATE CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000 WHERE ORDERS.CUSTOMER_ID = 3;
Verification
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows.
SELECT * FROM CUSTOMERS;
As we can see in the table below, SALARY value of “Kaushik” is increased by 1000 −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.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 |
The UPDATE… JOIN Clause in SQL Server
The SQL UPDATE… JOIN Clause also works in SQL Server database. But, the syntax of the query is slightly different from that of MySQL. However, the working of it is exactly the same as MySQL query.
In MySQL, the UPDATE statement is followed by the JOIN clause and SET statements respectively. Whereas, in MS SQL Server the SET statement is followed by the JOIN clause.
Syntax
Following is the syntax of the UPDATE… JOIN in SQL Server −
UPDATE tables(s) SET column1 = value1, column2 = value2, ... FROM table1 JOIN table2 ON table1.join_column = table2.join_column;
Example
In this example, we will update values of the CUSTOMERS and ORDERS table that we created above; using the following UPDATE… JOIN query −
UPDATE CUSTOMERS SET SALARY = SALARY + 1000 FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Verification
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows.
SELECT * FROM CUSTOMERS;
The updated CUSTOMERS table is displayed as follows −
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 |
”;