Multiple Tables in Update/Delete Statement

SQL SERVER – UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))INSERT INTO Table1 (Col1, Col2, Col3)SELECT 1, 11, 'First'UNION ALLSELECT 11, 12, 'Second'UNION ALLSELECT 21, 13, 'Third'UNION ALLSELECT 31, 14, 'Fourth'GO-- Create table2CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))INSERT INTO Table2 (Col1, Col2, Col3)SELECT 1, 21, 'Two-One'UNION ALLSELECT 11, 22, 'Two-Two'UNION ALLSELECT 21, 23, 'Two-Three'UNION ALLSELECT 31, 24, 'Two-Four'GO


Now let us check the content in the table.

SELECT *FROM Table1SELECT *FROM Table2
GO

Now let us see the following image. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. Additionally, we want to update the values of Col2 and Col3 only.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.

UPDATE Table1SET Col2 = t2.Col2,Col3 = t2.Col3FROM Table1 t1INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1WHERE t1.Col1 IN (21, 31)GO

Now let us select the data from these tables.

-- Check the content of the tableSELECT *FROM Table1SELECT *FROM Table2
GO

As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1DROP TABLE Table2
GO

SQL SERVER – DELETE From SELECT Statement – Using JOIN in DELETE Statement – Multiple Tables in DELETE Statement

This blog post is inspired from my earlier blog post of UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement. In the blog post I discussed about how we can use JOIN and multiple tables in the UPDATE statement. There were plenty of the emails after this blog post discussing about using JOIN in the DELETE statement as well using multiple tables in the DELETE statement.

It is totally possible to use JOIN and multiple tables in the DELETE statement. Let us use the same table structure which we had used previously.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))INSERT INTO Table1 (Col1, Col2, Col3)SELECT 1, 11, 'First'UNION ALLSELECT 11, 12, 'Second'UNION ALLSELECT 21, 13, 'Third'UNION ALLSELECT 31, 14, 'Fourth'GO-- Create table2CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))INSERT INTO Table2 (Col1, Col2, Col3)SELECT 1, 21, 'Two-One'UNION ALLSELECT 11, 22, 'Two-Two'UNION ALLSELECT 21, 23, 'Two-Three'UNION ALLSELECT 31, 24, 'Two-Four'GO


Now let us check the content in the table.

SELECT *FROM Table1SELECT *FROM Table2
GO

Now pay attention to following diagram. Here we have two tables Table1 and Table 2. Our requirement is that we want to delete those two records from Table1 where Table2 Col3 values are “Two-Three” and “Two-Four” and Col1 in both the tables are the same.

I have explained the above statement very easily in following diagram.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the DELETE statement and use multiple tables in the DELETE statement and do the task.

-- Delete data from Table1DELETE Table1FROM Table1 t1INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1WHERE t2.Col3 IN ('Two-Three','Two-Four')GO

Now let us select the data from these tables.

-- Check the content of the tableSELECT *FROM Table1SELECT *FROM Table2
GO

As you can see that using JOIN clause in DELETE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1DROP TABLE Table2
GO