Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Write Accesses 

INSERT, MODIFY dbtab - subquery_clauses

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { FROM source 

      FIELDS select_clause } 

  | { select_clause 

      FROM source } 

      [WHERE sql_cond] 

      [
GROUP BY group] [HAVING group_cond] 

      [
ORDER BY [UP TO n ROWS [OFFSET o]]] 

      [db_hints]  ...

Effect

Possible clauses and additions of a subquery after the addition FROM of the statements INSERT and MODIFY. These clauses define a result set, which is used as a data source of the INSERT or INSERT statement.

With the exception of the client column, the results set of the subquery must have at least as many columns as the primary key of the database table or the classic view filled using the INSERT statement or modified using the MODIFY statement. It cannot, however, have more columns than the target database table or view .

The following special rules apply:

  • FROM clause
  • If the database table or classic view edited using the INSERT or MODIFY statement is client-specific, at least one data source of the subquery must be client-specific.
  • If the subquery uses default client handling or if client handling is switched using USING CLIENT, implicit client handling in the INSERT statement or MODIFY statement cannot be switched using CLIENT SPECIFIED.
  • SELECT clause
  • Any SELECT lists of the subquery specified as comma-separated lists must define one column of the results set for at least every column of the primary key of the database table or classic view in question.

    If the subquery uses default client handling or switches client handling using USING CLIENT, no client column can be specified as the first column in the SELECT list. In other positions, client columns are not interpreted as client columns.

    If implicit client handling is switched in the subquery using USING [ALL] CLIENTS [IN], the first column of the SELECT list must be a client column.
  • In a SELECT list of the subquery specified using *, the results set produced for at least every column of the primary key of the database table or classic view in question must contain a column.

    If the subquery uses default client handling or switches client handling using USING CLIENT, the client column of the results set is ignored.

    If implicit client handling is switched in the subquery using USING [ALL] CLIENTS [IN], the client column of the results set is respected.
  • ORDER BY clause
  • An ORDER BY clause in a subquery is not supported by all databases. This means a syntax check warning from the extended program check can occur that can be hidden using the pragma ##db_feature_mode[limit_in_subselect_or_cte]. If this is detected at runtime on a database that does not support the pragma, a handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.

A further subquery within the subquery cannot access the table or view edited using the INSERT or MODIFY statement.


Notes

  • In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports ORDER BY clauses in subqueries. This requires the constant LIMIT_IN_SUBSELECT_OR_CTE of this class to be passed to the method in an internal table.
  • If a subquery is used, the ABAP SQL statement bypasses table buffering.
  • Implicit client handling of the subquery is not specified by client handling of the INSERT statement or MODIFY statement An addition USING or CLIENT SPECIFIED of the INSERT statement or MODIFY statement is ignored by the subquery. The subquery uses either the current client ID or a client specified in the subquery using USING. When the result of the subquery is written, however, implicit client handling of the INSERT statement or MODIFY statement applies by default. This can be defined using the additions USING CLIENT or CLIENT SPECIFIED.

  • If a query is used to access a CDS entity associated with a CDS role and for which CDS access control is not disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck or using the addition WITH PRIVILEGED ACCESS in the FROM clause, only that data is read implicitly that matches the access condition defined in the CDS role. If data cannot be read, ABAP programs cannot distinguish whether this is due to the conditions of the SELECT statement, the conditions of the CDS entity, or an associated CDS role. If the CDS database view database view of a CDS view is accessed, no access control takes place.


Example

Inserts columns of a results set formed by an inner join in a database table.

DELETE FROM demo_join1. 
INSERT demo_join1 
  FROM TABLE @( VALUE #( ( a = 'a1' b = 'b1' d = 'd1' ) 
                         ( a = 'a2' b = 'b2' d = 'd2' ) 
                         ( a = 'a3' b = 'b3' d = 'd3' ) ) ). 
DELETE FROM demo_join2. 
INSERT demo_join2 
  FROM TABLE @( VALUE #( ( d = 'd1' e = 'e1' f = 'f1' ) 
                         ( d = 'd2' e = 'e2' f = 'f2' ) 
                         ( d = 'd3' e = 'e3' f = 'f3' ) ) ). 

DELETE FROM demo_join3. 
INSERT demo_join3  FROM ( SELECT FROM demo_join1 AS d1 
                                  INNER JOIN demo_join2 AS d2 
                                    ON d1~d = d2~d 
                                FIELDS d1~a, d1~b, d2~e, d2~f ). 

SELECT * 
       FROM demo_join3 
       INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).