Operations with Query
If a query is stored in a database (a VIEW in SQL), it can be used in the SQL manipulation statements anywhere a table can be used. A query may also be used in a FOR statement or cursor declaration in a stored procedure.
However, there are many restrictions on query usage in operations that insert, delete or update data. The following table shows the various query usage:
| Construct |
INSERT |
DELETE |
UPDATE |
| JOIN operation |
Impossible |
Impossible |
Possible (if at least one operand allows this) |
| GROUP BY clause or aggregation functions |
Impossible |
Impossible |
Impossible |
| UNION operation |
Impossible |
Impossible |
Impossible |
| INTERSECT operation |
Impossible |
Possible (according to the first operand) |
Possible (according to the first operand) |
| EXCEPT operation |
Impossible |
Possible (according to the first operand) |
Possible (according to the first operand) |
| FOR READ ONLY clause |
Impossible |
Impossible |
Impossible |
| INSENSITIVE clause |
Impossible |
Impossible |
Impossible |
| Variables in SELECT or ORDER BY clauses |
Impossible |
Impossible |
Impossible |
Specifying more than one table in the FROM clause is a JOIN operation. Other operations, and operations used in a subquery do not have any affect on data operation.
If the query structure allows data to be updated (UPDATE), it does not necessarily mean that the data can be overwritten in all columns. You cannot update data in columns specified by a expression or data originating from uneditable operands in the JOIN operation.
