Java Reference
In-Depth Information
Figure 7-6:
Using dubqueries in the SELECT clause
Notice how the entire subquery replaces the column name, so that the alias clauses used to name the
columns appear outside the parentheses defining the subqueries.
Using a Subquery with the INSERT Command
You can use subqueries in the
INSERT
command just as easily as you can in a
SELECT
command.
Consider an example where you might want to insert selected records from one table into another.
One way to do this is to use a subquery to select the desired subset from the source table.
The following example uses a subquery to select the Customer_Numbers of customers from New
Jersey. Then the appropriate fields are selected from Customers with the selected
Customer_Numbers and are inserted into the Employees table.
INSERT INTO Employees
(Employee_ID, First_Name, Last_Name)
SELECT Customer_Number, First_Name, Last_Name
FROM Customers
WHERE Customer_Number IN
(SELECT Customer_Number
FROM Customers
WHERE State = 'NJ');
Using a Subquery with the UPDATE Command
A more common usage of the subquery is with the
UPDATE
command. One advantage of using a
subquery is that you can very easily test the subquery by itself to make sure you are getting the correct
data set. Then, once it checks out OK, you can plug it into the actual update.
The following example uses a subquery to select the Customer_Number of the customer to be
updated from the Customers table. You then use this customer number in the
WHERE
clause of the
UPDATE
command.
UPDATE Employees
