How To Update Table Using JOIN in SQL Server

In this SQL Server tutorial, we will discuss how to update a table using join in SQL Server.

Recently, I was working on the Amazon Order Payment System in an Amazon Database. And I found out that I had updated some records in the table using the JOIN clause. Using the UPDATE JOIN statement, this is accomplished.

Here are the topics that we are going to discuss:

• How to use SQL Server UPDATE statement with the JOIN clause on tables?
• How do update tables by using the INNER JOIN clause in SQL Server?
• How to update multiple tables with JOIN clauses in SQL Server?
• How to update the table with the SELF JOIN clause in SQL Server?
• How to delete a table with a JOIN clause in SQL Server?

Update Table Using Join in SQL Server

To query data from related tables, we often use join clauses, either an INNER JOIN or a LEFT JOIN. Using these join clauses in the UPDATE statement in SQL Server will allow you to update multiple tables at once.

SYNTAX:

UPDATE TABLE_1
SET TABLE_1=NEW_VALUE_1
TABLE_2=NEW_VALUE_2
FROM TABLE_1
[ INNER | LEFT ] TABLE_2
ON JOIN_PREDICTION
WHERE
WHERE_PREDICATE;

In the syntax explanation:

• First, we need to specify the table_1 from which the data needs to be updated.
• Next, specify the new value of each column from the updated table.
• Then, we need to specify the table from which we want to update in the FROM clause.
• After that, we can use the INNER JOIN or LEFT JOIN clause to join another table (TABLE_2) using the join_precdicate which is specified after the ON keyword.
• Finally, the WHERE condition is optional. And specify the rows which will be updated.

Here is the ER-diagram image of the Amazon Orders Payment System which is shown below:

Here are two tables i.e; PRODUCTS and ORDER_ITEMS which are used for the UPDATE JOIN operation. Here are the images given below:

EXAMPLE:

UPDATE ORDER_ITEMS
SET ORDER_ITEMS.ORDER_AMOUNT = ORDER_ITEMS.QUANTITY * PRODUCTS.PRICE
FROM ORDER_ITEMS
INNER JOIN PRODUCTS
on ORDER_ITEMS.PRODUCT_ID = PRODUCTS.ID;

SELECT * FROM ORDER_ITEMS;

With the help of the UPDATE statement, we have got the total amount of the order will multiplying the quantity and price column in the result set.

Once the UPDATE statement has been executed then use the SELECT statement to retrieve the updated value of all records and also the new value of the ORDER_AMOUNT column in the ORDER_ITEMS table.

We hope that you have understood how to use the UPDATE statement with the JOIN clause on tables by the query.

Read: SQL Server OUTER JOIN

Update Multiple Tables Using Join in SQL Server

Here, we will see an example of how to update multiple tables using the JOIN clause in SQL Server by the following query.

So, there is no method of the UPDATE JOIN statement on the multiple tables in the SQL Server. But if we want to write it then we have to use the BEGIN TRANSCATION in beginning and the COMMIT statement in end for executing the UPDATE JOIN statement.

EXAMPLE:

BEGIN TRANSACTION;

UPDATE ORDERS
SET ORDERS.STATUS=PRODUCTS.STATUS
FROM ORDERS 
INNER JOIN ORDER_ITEMS
ON ORDERS.ID=ORDER_ITEMS.ORDER_ID
INNER JOIN PRODUCTS
ON ORDER_ITEMS.PRODUCT_ID=PRODUCTS.ID;

UPDATE ORDER_ITEMS
SET ORDER_ITEMS.ORDER_AMOUNT = ORDER_ITEMS.QUANTITY * PRODUCTS.PRICE
FROM ORDER_ITEMS
INNER JOIN PRODUCTS
on ORDER_ITEMS.PRODUCT_ID = PRODUCTS.ID
WHERE ORDER_ITEMS.QUANTITY>1;

COMMIT;

SELECT * FROM ORDERS; 

SELECT * FROM ORDER_ITEMS;

Here is the query explanation says:

• In the first UPDATE JOIN statement, we have updated the STATUS column value as YES or NO based on the ORDERS, PRODUCTS and ORDERS_ITEMS tables and matching records based on the common column from all these tables.
• In the second UPDATE JOIN statement, we have calculated and updated the value of the ORDER_AMOUNT column based on the quantity greater than 1 in the WHERE clause condition.
• Once the COMMIT statement has been executed, use the SELECT statement to retrieve the updated value of all columns from the ORDERS and ORDER_ITEMS tables.

We hope that you have understood how to use an update on multiple tables with the JOIN clause in SQL Server.

Read: RIGHT JOIN in SQL Server

Update Table With Self Join in SQL Server

As we know, the SELF JOIN clause is used to join a table to itself. It makes it easier to compare rows inside the same database or query hierarchical data. Normally, it uses the INNER JOIN or LEFT JOIN clause.

Here, we have used the CATEGORIES table for the SELF JOIN clause which is shown below image:

EXAMPLE:

UPDATE CA
SET CA.CAT_NAME='Phone With Cover'
FROM CATEGORIES CA
INNER JOIN CATEGORIES C
ON CA.ID=C.PARENT_ID
WHERE C.ID=5;

SELECT * FROM CATEGORIES;

With the help of the UPDATE JOIN statement, we have self-updated the value of the CAT_NAME column as PHONE WITH COVER based on the WHERE condition.

Once the UPDATE statement has been updated, the SELECT statement will retrieve all records from the CATEGORIES table.

We hope that you have understood how to use the UPDATE statement with the SELF JOIN clause on a table by the query.

Read: What is SQL Server Cross Join?

Delete Table Using Join in SQL Server

It is not a straightforward operation in SQL Server. The WHERE clause may also need us to occasionally update or delete records from the table. Therefore, we use the SQL Server DELETE JOIN clause on three tables. These tables are TARGET_TABLE, TABLE_1, and TABLE_2.

So, the TARGET_TABLE is a table from which we will delete records by using the DELETE JOIN statement.

SYNTAX:

DELETE TABLE_1 AS TARGET_TABLE
FROM TABLE_1
JOIN TABLE_2 ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
WHERE [ CONDITIONS ]; 

Here are two tables i.e; COUNTRIES and USERS table which will be used with the DELETE JOIN clause:

EXAMPLE:

DELETE COUNTRIES
FROM COUNTRIES
INNER JOIN USERS
ON USERS.COUNTRY_CODE=COUNTRIES.CODE
WHERE NAME='United State of America';

SELECT * FROM COUNTRIES;

By using the DELETE JOIN clause in the resultset, we have deleted the name United States of America from the COUNTRIES table and with a matching record from the COUNTRY table.

Once the record has been deleted by using the DELETE JOIN clause, use the SELECT statement to retrieve the remaining records from the COUNTRIES table.

We hope that you have understood how to use the DELETE statement with the INNER JOIN clause on tables in SQL Server.

You may also like to read the following SQL Server.

• SQL Server Row_Number Join
• Recursive Trigger in SQL Server
• Debug an SQL Server Trigger
• SQL Server Trigger On View

We now know about the post “Update Table Using Join SQL Server” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

• How to use SQL Server UPDATE statement with the JOIN clause on tables?
• How do update tables by using the INNER JOIN clause in SQL Server?
• How to update multiple tables with JOIN clauses in SQL Server?
• How to update the table with the SELF JOIN clause in SQL Server?
• How to delete a table with a JOIN clause in SQL Server?