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.
When to use Subqueries Review
Subqueries can be used in the following situations:
After an IN keyword
After a NOT IN keyword
After an EXISTS keyword
After a NOT EXISTS keyword
After an ANY keyword
After a SOME keyword
After an ALL keyword
When comparing values
Where any expression can be used
A subquery is most commonly used in place of joining two tables together. Generally, there is no performance difference between using joins with multiple tables or using subqueries, with one exception: if the subquery contains an EXISTS clause, it would be preferable to construct a join.
Subquery Syntax:
Subqueries can be used with the following syntax (which is shown as a subset of the DELETE syntax):
DELETE FROM table
[FROM table [,…]]
WHERE column [NOT] IN|[NOT]
EXISTS|ANY|SOME|ALL|Comparison (subquery)
The important thing to remember when using a subquery is that the subquery will return one or more values. You can determine if the whole query might fail by first executing the subqueries individually. Doing so will let you understand the values that are returned by each subquery. Then, it might be a good idea to include those values into the outermost query by using literals. This way, it makes your complicated queries manageable. You can use a subquery anywhere that an expression is required within a DELETE Transact-SQL statement’s WHERE clause.
An example of a subquery used with an INSERT statement would be this Transact-SQL statement:
Insert Statement using Subqueries
1) Break this code down into pieces. There are two main sections of code.
INSERT INTO SalaryHistory(LastName, FirstName,
PriorSalary)
SELECT LastName, FirstName, Salary
FROM Employee
WHERE LastName = 'Mouse'
AND FirstName = 'Micky'
AND NOT EXISTS
2) The first is an INSERT statement
3) The insert statement gets its values by using a SELECT statement. This is NOT a subquery, but part of most INSERT statements.
4) The second section of code is the actual subquery which is within the NOT EXISTS clause. This clause is used to test for the existence of something.
5) In this case, one of the conditions of the INSERT statement is to test for the existence of the FirstName of Micky, LastName of Mouse, and PriorSalary of 20000.
6) Therefore, data will only be inserted if there is not already a record containing this data in the SalaryHistory table.
Why are server-side cursors important?
For over a decade now SQL Server and other DBMS developers have been using server-side
cursors to access their databases and scroll through updatable rowsets. This connected approach assumes that the database connection remains in place while the application runs a query and builds a server-side set of rows that can be retrieved and updated as needed. For a litany of reasons, Microsoft chose not to implement any server-side cursor functionality in their new .NET Framework data access interface ADO.NET. This page discusses how you can work around this limitation to create and manage your own server-side cursors.
Server-side cursors are especially useful when working with highly interactive applications especially when the application cannot work with disconnected (static) data. This type of application needs a mechanism to work with a single row or a small set of rows at once.
Server-side cursors are designed to meet this need. As illustrated by the examples in this article, I will show you how to:
Create a cursor based on a focused SELECT statement.
Position a cursor to any designated row.
Change the data in the currently selected cursor row.
Adapt the cursor so other users changes are visible (or not).
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.
