| 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.