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
In the next lesson, you will learn about indexing strategies.