CRUD Statements  «Prev  Next»
Lesson 12 Using subqueries
Objective Use subqueries with your INSERT, UPDATE, and DELETE statements.

Using Sub Query Statements

You learned how to use subqueries in the SELECT statement module.
This lesson will show you how to use subqueries with your INSERT, UPDATE, and DELETE statements.

Using a subquery with an INSERT statement

An example of a subquery used with an INSERT statement would be this Transact-SQL statement:


Insert Statement using SubQuery
You can use a subquery anywhere that an expression is required within an INSERT Transact-SQL statement, as long as it does not appear in the ORDER BY clause of these statements.

Using a subquery with an UPDATE statement


An example of a subquery used with an UPDATE statement would be this Transact-SQL statement:
UPDATE Employee
SET CurrentSalary = (SELECT Max(CurrentSalary) * 1.5 
FROM Employee)
WHERE LastName = 'Mouse'
AND FirstName = 'Mickey'

How about an explanation? Well, it is quite simple. There is a SELECT query within the UPDATE query. The SELECT query returns a single value, which is the maximum value in the CurrentSalary column of the Employee table, plus 50% more. When this SELECT query returns, it will set the CurrentSalary column to that value, but only for data where the LastName is Mouse and FirstName is Mickey.

Using a subquery with a DELETE statement

An example of a subquery used with a DELETE statement would be this Transact-SQL statement:

DELETE FROM SalaryHistory
WHERE EmployeeID IN (SELECT EmployeeID 
FROM Employee WHERE LastName = 'Mouse')

In this example, there is a SELECT query within the DELETE query. Because the subquery specifies only a LastName value, the subquery can return multiple values (such as Mickey and Minnie). These multiple values are then used within the IN statement of the WHERE clause.

Create delete Statement using Subquery - Exercise

Click the Exercise link below to practice deleting data with values from another table.
Create Delete Statement using Subquery - Exercise

In the next lesson, you will learn about indexing strategies.