|
See
Also
Subqueries are used in two
ways:
|
·
|
In SELECT, UPDATE and DELETE statements, you can use subqueries
in WHERE clauses to determine the subset of records to which the
statement is to be applied. To define these WHERE subqueries, see
"How to Specify a WHERE Condition". |
|
·
|
In INSERT and UPDATE statements, you can use subqueries to
compute the values to be inserted or updated. These statements take
the following forms respectively: |
INSERT ... SELECT ...
UPDATE ... SET ... = <subquery> [WHERE
...]
To build an INSERT ... SELECT
statement:
| 1.
|
Start the SQL Definer specifying statement type Insert (see
"How to Start the SQL Definer"). |
| 2.
|
Select the table you want to insert into (see "How to Select
Tables"). |
| 3.
|
Specify output columns (see "How to Specify Output
Columns"). |
| 4.
|
Select the Column Definitions tab in the Select List pane. |
| 5.
|
In the Value field for one of the output columns, type
SUBQUERY([<name>]). The subquery name is optional. It will
help you recognize it in the Query Tree but has no other
function. |
| 6.
|
Click the subquery node in the Query Tree. You can now define
the subquery as you would define a main query. |
NOTES:
If you use a subquery for values, there can be only one
subquery, which should provide all the values required for the
INSERT.
|
·
|
Constants or expressions can not be used and are therefore
ignored. They should become a part of the subquery. |
|
·
|
Because most subqueries refer to the table in the main
statement (correlated subquery), the SQL Definer places this table
in the From List by default; this is not necessarily helpful in the
case of INSERT. If it is not, begin by removing this correlated
table. |
To build an UPDATE .. SET ...
<subquery> [WHERE ...] statement:
| 1.
|
Start the SQL Definer specifying statement type Update (see
"How to Start the SQL Definer"). |
| 2.
|
Select the table you want to update (see "How to Select
Tables"). |
| 3.
|
As output column, specify the column you want to update (see
"How to Specify Output Columns"). |
| 4.
|
Select the Column Definitions tab in the Select List pane. |
| 5.
|
In field Value of the output column, type
SUBQUERY([<name>]). The subquery name is optional. It will
help you recognize it in the Query Tree but has no other
function. |
| 6.
|
Click on the subquery node in the Query Tree. You can now
define the subquery as you would define a main query. Because most
subqueries refer to the table in the main statement (correlated
subquery), the SQL Definer places this table in the From List by
default. |
NOTE:
Multiple subqueries are possible. Constants and expressions can
also be used for certain columns.
|
