Delete and Update Rows Using Inner Join in SQL Server

Posted September 22, 2015 by Vishwanath Dalvi in Database, SQL Server

feature-sql1

Inner join is used to select rows from multiple tables based on a matching column in one or more tables. It compares each row value of a table with each row value of another table to find equal values. If equal value are found in multiple columns from multiple tables, they are returned in the result set.

Basic Inner Join Syntax

SELECT T1.Columns, T2. Columns
FROM   Table1 as T1 INNER JOIN Table2 T2
ON     T1.Pk_Table1_Rowid = T2.Table2_Rowid;

 

Inner Join Example

IF OBJECT_ID ('Table1', 'U') IS NOT NULL
DROP TABLE Table1

CREATE TABLE Table1
(
Id INT,
FruitName Varchar( 100)
);

IF OBJECT_ID ('Table2', 'U') IS NOT NULL
DROP TABLE Table2

CREATE TABLE Table2
(
Id INT,
FruitName Varchar( 100)
);

Insert into Table1 Values ( 1,'Mango' ), ( 2, 'Graps'), (3, 'Banana');
Insert into Table2 Values ( 1,'Mango' ), ( 2, 'Graps'), (4, 'Orange');

SELECT * FROM Table1 ;
SELECT * FROM Table2 ;

SELECT *
FROM   Table1 T1 Inner Join Table2 T2
ON     T1. Id = T2 .Id;

 

Update Using Inner Join in SQL Server

Using the same concept of Inner join, we can update rows in one table based on another table using Inner Join.

Syntax for Update with Inner Join

UPDATE  T2
SET    T2. Name = T1 .Name
FROM   Table2 as T2 INNER JOIN Table1 as T1
ON     T1. Id = T1 .Id;

 

To simplify syntax, T2 is an alias name for Table2, whose rows we want to update based on matching rows with Table1. On clause specifies the column names to find matching rows between both tables using Inner Join. SET specifies the Table2 column Name will be updated with values of Table1 name column.

Update with Inner Join Example

IF OBJECT_ID ( 'Table1', 'U' ) IS NOT NULL
DROP TABLE Table1

CREATE TABLE Table1
(
Id INT,
FruitName Varchar( 100 )
);

IF OBJECT_ID ( 'Table2', 'U' ) IS NOT NULL
DROP TABLE Table2

CREATE TABLE Table2
(
Id INT,
FruitName Varchar( 100 )
);

Insert into Table1 Values ( 1 ,'Mango' ), ( 2 , 'Graps' ), ( 3, 'Banana');
Insert into Table2 Values ( 1 ,'Mango' ), ( 2 , NULL ), ( 3, NULL );

SELECT * FROM Table1 ;
SELECT * FROM Table2 ;

UPDATE T2
SET    T2. FruitName = T1 .FruitName
FROM   Table2 as T2 INNER JOIN Table1 as T1
ON     T1. Id = T2 .Id;

SELECT * FROM Table1 ;
SELECT * FROM Table2 ;

 

In the example above, NULL values rows in Table2 are updated with Table1 rows based on a matching ID column.

Before Update with Inner Join

Update with Inner Join

After Update with Inner Join

After Update with Inner Join

Delete Using Inner Join

Using the same concept of Inner join, we can delete rows from one table based on another table using Inner Join.

Syntax for Delete with Inner Join

DELETE  T2
FROM   Table2 as T2 INNER JOIN Table1 as T1
ON     T1. Id = T1 .Id;

 

To simplify syntax, T2 is an alias name for Table2, whose rows we want to delete based on matching rows with Table1. On clause specifies columns names to find matching rows between both tables using Inner Join.

IF OBJECT_ID ( 'Table1', 'U' ) IS NOT NULL
DROP TABLE Table1

CREATE TABLE Table1
(
Id INT,
FruitName Varchar( 100 )
);

IF OBJECT_ID ( 'Table2', 'U' ) IS NOT NULL
DROP TABLE Table2

CREATE TABLE Table2
(
Id INT,
FruitName Varchar( 100 )
);

Insert into Table1 Values ( 1 ,'Mango' ), ( 2 , 'Graps' ), ( 3, 'Banana'), (4, 'WaterMelon'), (5, 'Orange');
Insert into Table2 Values ( 1 ,'Mango' ), ( 2 , 'Graps'), (3 , 'Banana' ), ( 6, 'Pear'), (7, 'Papaya');

SELECT * FROM Table1 ;
SELECT * FROM Table2 ;

DELETE T2
FROM   Table2 as T2 INNER JOIN Table1 as T1
ON     T1. Id = T2 .Id;

SELECT * FROM Table1 ;
SELECT * FROM Table2 ;

 

In the example above, Rows with Id (1,2,3) are deleted from table T2 because it matches with Table1 (Id) Column with Inner join.

Before Delete with Inner Join

Before Delete with Inner Join

After Delete with Inner Join

After Delete with Inner Join

Currently SQL server does not support deleting rows from both the tables using one delete statement like other RDBMS.

See also SQL Server Archives

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.