Using a subquery in insert, update, and delete statements

By: Rick Dobson

Overview

Up until this point, the tutorial demonstrated how to use subqueries with a WHERE clause in a SELECT statement as well as in select list items.  This concluding subsection on introducing subqueries presents examples for using subqueries in INSERT, UPDATE, and DELETE statements.

Using a subquery in insert, update, and delete statements

The first demonstration is for the populating of a local temp table with a bulk INSERT statement based on a SELECT statement with subquery.  The code sample below starts by creating a fresh copy of the #bikes_subcategories local temp table.  The table has three columns named ProductCategoryID, cat_name, and subcat_name.

After creating the #bikes_subcategories local temp table, the code invokes a bulk INSERT statement based on a SELECT statement with a subquery.  The subquery extracts the ProductCategoryID with a value of 1 from the ProductCategory table in the Production schema of the AdventureWorks2014.  ProductCategoryID value 1 denotes the bikes category.  This category has three subcategories with names of Mountain Bikes, Road Bikes, and Touring Bikes.

The outer query referencing the subquery has a source based on the join of the ProductCategory and ProductSubcategory tables in the Adventureworks2014 database.  The outer query select list items include ProductCategoryID, cat_name from the Name column in the ProductCategory table, and subcat_name from the Name column in the ProductSubcategory table.  A WHERE clause in the outer query extracts rows from the join when their ProductCategoryID value equals the value returned by the subquery, which is 1 in the sample below. 

-- create fresh version of #bikes_subcategories tables
BEGIN TRY
  --drop table #MaleStudents --#bikes_subcategories
  DROP TABLE #bikes_subcategories
END TRY
BEGIN CATCH
  PRINT '#bikes_subcategories not available to drop'
END CATCH
GO

CREATE TABLE #bikes_subcategories (
  ProductCategoryID int,
  cat_name nvarchar(50),
  subcat_name nvarchar(50)
)

-- insert into temp table from 
-- a select statement with a subquery
INSERT INTO #bikes_subcategories
  -- a subquery in a where clause that returns 1 row
  SELECT
    cat.ProductCategoryID,
    cat.Name cat_name,
    subcat.Name subcat_name
  FROM [AdventureWorks2014].[Production].[ProductCategory] cat
  INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcat
    ON cat.ProductCategoryID = subcat.ProductCategoryID
  WHERE cat.ProductCategoryID =
  -- the code in parentheses is the subquery
  (SELECT
     cat.ProductCategoryID
   FROM [AdventureWorks2014].[Production].[ProductCategory] cat
   WHERE cat.ProductCategoryID = 1
  )

-- display rows inserted into #bikes_subcategories
SELECT *
FROM #bikes_subcategories

The next screen shot shows the three rows returned from a select statement for all columns in the #bikes_subcategories local temp table.

• The three rows in the Results tab below all have a ProductCategoryID value of 1; this is the value returned by the subquery in the preceding script.
• The ProductCategoryID value of 1 points to a category named Bikes.  Therefore, the cat_name column value is Bikes for all rows in the table.
• The subcat_name column displays subcategory names for the Bikes category.  The subcategory names are: Mountain Bikes, Road Bikes, and Touring Bikes.

The next code sample shows how to use a subquery with an UPDATE statement.  The UPDATE statement changes the name of the subcategory value for a row in a table.  The following code sample assigns the value of Racing Bikes to the subcat_name column in the #bikes_subcategories local temp table.  The update keyword points to the temp table, and the set statement identifies the column to which to assign Racing Bikes.  The WHERE keyword accepts a filter based on the subquery; this filter identifies rows in which to perform the update.

-- update 'Road Bikes' subcat_name to 'Racing Bikes' 
-- subcat_name from #bikes_subcategories
UPDATE #bikes_subcategories
SET subcat_name = 'Racing Bikes'
WHERE subcat_name IN (SELECT Name subcat_name
                      FROM [AdventureWorks2014].[Production].[ProductSubcategory]
                      WHERE [ProductCategoryID] = 1
                      AND Name = 'Road Bikes'
                     )

-- display #bikes_subcategories after update
SELECT *
FROM #bikes_subcategories

The following Results tab shows the three rows from the local temp table after the update statement.  By comparing this Result tab to the preceding Result tab, you can verify the operation of the UPDATE statement based on the subquery.  Notice that the second row has a subcat_name value of Racing Bikes in the following Result tab, but the subcat_name value for the second row is Road Bikes.

The final example for this introduction to subqueries tutorial section demonstrates the use of a subquery in combination with a DELETE statement.  The code sample below removes a row from the #bikes_subcategories local temp table.  The subquery designates the row to delete, and the delete keyword points at the temp table from which to delete the row.  The subquery specifies the row with a subcat_name value of Racing Bikes.  The DELETE statement removes the one row in the #bikes_subcategories local temp table with a value of Racing Bikes.

-- delete 'Road Bikes' subcat_name from #bikes_subcategories
DELETE #bikes_subcategories
WHERE subcat_name IN (SELECT subcat_name
                      FROM #bikes_subcategories
                      WHERE [ProductCategoryID] = 1
                      AND subcat_name = 'Racing Bikes'
                     )

-- display rows remaining #bikes_subcategories
SELECT *
FROM #bikes_subcategories

Here’s a Results tab that shows the rows in the #bikes_subcategories local temp table after the operation of the DELETE statement.  Because one row is deleted, there are now two rows instead of three rows in the table.  Notice it is the row with a subcat_name value of Racing Bikes that is removed from the temp table.