Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses 

SELECT - SINGLE

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... SINGLE [FOR UPDATE] ...

Addition

... FOR UPDATE

Effect

The ABAP-specific addition SINGLE makes the results set of a query a single row set. The addition is possible with a standalone SELECT statement or with the main query of a standalone WITH statement. If SINGLE is specified, these statements does not open a loop closed using ENDSELECT or ENDWITH during imports to a non-table-like target area, and no internal tables can be specified as a target area.

  • If the selection of the SELECT statement covers precisely one row, this row is included in the results set.
  • If the selection of the SELECT statement covers more than one row, one of these rows is included in the results set at random.

The following restrictions apply:


Notes

  • If no unique row is identified, the addition SINGLE can also be used to detect whether a corresponding row exists. In this case, the warning from the extended program check must be hidden using a pragma. To avoid unnecessary transports of data, a SELECT list can also be used that contains nothing but a single constant (see the executable example).
In the case of rows specified in full, a SELECT statement with the addition SINGLE is generally faster than specifying only part of the row.
  • The results set of SELECT statements with the addition SINGLE matches the set from the addition UP TO 1 ROWS without using the addition ORDER BY.
  • If the addition SINGLE is used, it is not necessary to use the statements ENDSELECT, ENDWITH or to import the row into an internal table. Not all additions of the SELECT statement, however, can be used.
  • If the addition UP TO 1 ROWS is used, the statement ENDSELECT or ENDWITH must be specified or the row must be imported into an internal table. The addition ORDER BY can, however, be specified.
A SELECT statement with the addition SINGLE can be optimized for reading a single row, which means is generally somewhat faster than when using the addition UP TO 1 ROWS. In practice, however, this difference can usually be ignored. In light of this, the following is recommended:
  • Use of the addition SINGLE to read a row specified in full precisely.
  • Use of the addition UP TO 1 ROWS to read a maximum of one row from a set of selected rows.
  • The addition ORDER BY cannot be used together with SINGLE, which means that it is not possible to define which row is read from a non-unique selection. Instead, the addition UP TO 1 ROWS can be specified with the addition ORDER BY to define which row is read from a non-unique selection.
  • If SINGLE is specified and LOB handles are created in the INTO clause, all primary key fields joined by AND in logical expressions must be checked for equivalence in the WHERE condition. If this is not possible, the addition UP TO 1 ROWS can be used instead of SINGLE.
  • If the addition SINGLE is used, after the creation of LOB handles, all reader streams which are created when executing the statement SELECT, as well as locators, continue to exist until they are closed, either explicitly with one of their methods, or implicitly at the end of the current database LUW. The associated database operation is not completed during this time. It is best to close all LOB handles as soon as possible.


Example

Reads the row with the information about Lufthansa flight 0400 from the database table SPFLI.

SELECT SINGLE * 
       FROM spfli 
       WHERE carrid = 'LH' AND 
             connid = '0400' 
       INTO @DATA(wa).

Example

The program DEMO_SELECT_SINGLE_VS_UP_TO compares the performance of SELECT statements with the addition SINGLE with similar statements with the addition UP TO 1 ROWS.

Addition

... FOR UPDATE

Effect

When reading an individual row using SINGLE, the addition FOR UPDATE sets a database lock as an exclusive lock for this row on the database. The statement SELECT is then only executed if, in the WHERE condition, all primary key fields in logical expression which are joined using AND are checked for equivalence. Otherwise the results set is empty and sy-subrc is set to 8.

The addition FOR UPDATE can be used only to access data sources that allow writes and cannot be specified when accessing views that allow only reads.


Notes

  • If set incorrectly, the lock can produce a deadlock.
  • If the addition FOR UPDATE is used, a standalone SELECT statement bypasses table buffering.


Example

In the following example, the exclusive lock set by the statement DELETE is resolved using a database commit. This means that an exclusive lock is applied by the statement SELECT rather than waiting until the statement UPDATE is executed.

DELETE FROM demo_expressions. 
INSERT demo_expressions FROM @( VALUE #( id = 'X' ) ). 
COMMIT CONNECTION default. 

... 

SELECT SINGLE FOR UPDATE id, num1 
       FROM demo_expressions 
       WHERE id = 'X' 
       INTO @DATA(wa). 

... 

UPDATE demo_expressions SET num1 = 111 WHERE id = 'X'.