Manipulating data under SQL without a cursor

SQL statements for querying and updating data can be classified as followed:

• SQL statements which access rows via a cursor The function of a cursor and the SQL statements which address a cursor are described in section “Data manipulation under SQL using cursors”.

• SQL statements which access rows without using a cursor The following SQL statements do not use a cursor:

  SELECT	SELECT is used to select values from a row and pass them to host variables (see "Host variables")
  INSERT	Insert rows in a table
  MERGE	Combine the functions INSERT and UPDATE in one operation. Depending on the result of the search condition, MERGE changes column values of records which already exist or adds new records to an existing table.
  UPDATE	Update column values in selected rows of a table
  DELETE	Delete rows from a table

The following conditions must be fulfilled in order to make changes to a table with the statements mentioned above:

• the user must have the appropriate privileges (see "Access protection based on privileges in SQL"); the SELECT privilege is also required when changes are made using a query expression or a search condition.

• the transaction mode (see "SQL transaction") for the current transaction must be READ WRITE

• the DBH start statement ADD-SQL-DATABASE-CATALOG-LIST (see the “ Database Operation” manual) must have set ACCESS=*PARAMETERS (WRITE=*YES) or ACCESS=*PARAMETERS (CAT-ADMINISTRATION=*YES).