How to Build Subqueries into INSERT or UPDATE Statements

Previous Next

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.

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.