ABAP INSERT, UPDATE, DELETE: Modifying Database Tables

INSERT – Inserting New Records

Syntax

" Insert single row
INSERT INTO <dbtable> VALUES <work_area>.

" Insert multiple rows from internal table
INSERT <dbtable> FROM TABLE <internal_table>
       [ACCEPTING DUPLICATE KEYS].

System Fields

sy-subrc:
- 0: Insert successful.
- 4: Key already exists (duplicate).
sy-dbcnt: Number of inserted rows.

Examples

Insert Single Row

DATA: ls_customer TYPE zcustomer.

ls_customer-id = '1001'.
ls_customer-name = 'Miller GmbH'.
ls_customer-city = 'Berlin'.
ls_customer-created_at = sy-datum.

INSERT INTO zcustomer VALUES @ls_customer.

IF sy-subrc = 0.
  WRITE: / 'Customer successfully created.'.
ELSE.
  WRITE: / 'Error: Customer already exists.'.
ENDIF.

Insert Multiple Rows

DATA: lt_customers TYPE TABLE OF zcustomer.

lt_customers = VALUE #(
  ( id = '1002' name = 'Schmidt AG'  city = 'Munich' )
  ( id = '1003' name = 'Weber KG'    city = 'Hamburg' )
  ( id = '1004' name = 'Fischer GmbH' city = 'Cologne' )
).

INSERT zcustomer FROM TABLE @lt_customers.

WRITE: / 'Inserted rows:', sy-dbcnt.

With ACCEPTING DUPLICATE KEYS

" Ignores duplicates, only inserts new rows
INSERT zcustomer FROM TABLE @lt_customers
       ACCEPTING DUPLICATE KEYS.

IF sy-subrc = 4.
  WRITE: / 'Some rows already existed.'.
ENDIF.

WRITE: / 'Successfully inserted:', sy-dbcnt.

UPDATE – Modifying Existing Records

Syntax

" Update row by key (complete row)
UPDATE <dbtable> FROM <work_area>.

" Update multiple rows from internal table
UPDATE <dbtable> FROM TABLE <internal_table>.

" Update specific fields with SET
UPDATE <dbtable>
   SET <field1> = <value1>, <field2> = <value2>, ...
   WHERE <condition>.

System Fields

sy-subrc:
- 0: At least one row updated.
- 4: No matching row found.
sy-dbcnt: Number of updated rows.

Examples

Update Single Row (Complete Row)

DATA: ls_customer TYPE zcustomer.

" First read
SELECT SINGLE * FROM zcustomer
  WHERE id = '1001'
  INTO @ls_customer.

IF sy-subrc = 0.
  " Modify
  ls_customer-city = 'Frankfurt'.
  ls_customer-updated_at = sy-datum.

  " Write back
  UPDATE zcustomer FROM @ls_customer.

  IF sy-subrc = 0.
    WRITE: / 'Customer updated.'.
  ENDIF.
ENDIF.

Update Multiple Rows

DATA: lt_customers TYPE TABLE OF zcustomer.

" Load and modify customers
SELECT * FROM zcustomer
  WHERE city = 'Berlin'
  INTO TABLE @lt_customers.

LOOP AT lt_customers ASSIGNING FIELD-SYMBOL(<fs_cust>).
  <fs_cust>-region = 'East'.
ENDLOOP.

" Write back all changed rows
UPDATE zcustomer FROM TABLE @lt_customers.

WRITE: / 'Updated rows:', sy-dbcnt.

With SET and WHERE (Mass Update)

" Set all customers in Berlin to region 'East'
UPDATE zcustomer
   SET region = 'East',
       updated_at = @sy-datum
   WHERE city = 'Berlin'.

WRITE: / 'Updated rows:', sy-dbcnt.

Calculate Values with SET

" Increase prices by 10%
UPDATE zproduct
   SET price = price * '1.1',
       updated_at = @sy-datum
   WHERE category = 'ELECTRONICS'.

" Increment counter
UPDATE zcounter
   SET count = count + 1
   WHERE id = 'VISITOR'.

DELETE – Deleting Records

Syntax

" Delete single row by key
DELETE <dbtable> FROM <work_area>.

" Delete multiple rows from internal table
DELETE <dbtable> FROM TABLE <internal_table>.

" Delete rows with WHERE condition
DELETE FROM <dbtable> WHERE <condition>.

System Fields

sy-subrc:
- 0: At least one row deleted.
- 4: No matching row found.
sy-dbcnt: Number of deleted rows.

Examples

Delete Single Row

DATA: ls_customer TYPE zcustomer.

ls_customer-id = '1001'.

DELETE zcustomer FROM @ls_customer.

IF sy-subrc = 0.
  WRITE: / 'Customer deleted.'.
ELSE.
  WRITE: / 'Customer not found.'.
ENDIF.

Delete Multiple Rows

DATA: lt_customers TYPE TABLE OF zcustomer.

" IDs of customers to delete
lt_customers = VALUE #(
  ( id = '1002' )
  ( id = '1003' )
).

DELETE zcustomer FROM TABLE @lt_customers.

WRITE: / 'Deleted rows:', sy-dbcnt.

With WHERE Condition (Mass Deletion)

" Delete all inactive customers
DELETE FROM zcustomer
  WHERE status = 'INACTIVE'
    AND last_order_date < '20200101'.

WRITE: / 'Deleted rows:', sy-dbcnt.

" CAUTION: Without WHERE, ALL rows are deleted!
" DELETE FROM zcustomer.  " Deletes entire table!

MODIFY – Insert or Update (Upsert)

The MODIFY statement combines INSERT and UPDATE:

If key exists → Row is updated
If key does not exist → Row is inserted

Syntax

" Single row
MODIFY <dbtable> FROM <work_area>.

" Multiple rows
MODIFY <dbtable> FROM TABLE <internal_table>.

System Fields

sy-subrc: Always 0 (except for database errors).
sy-dbcnt: Number of inserted/updated rows.

Examples

DATA: ls_config TYPE zconfig.

ls_config-key = 'MAX_USERS'.
ls_config-value = '100'.
ls_config-updated_at = sy-datum.

" Inserts if not present; updates if present
MODIFY zconfig FROM @ls_config.

WRITE: / 'Configuration saved.'.

DATA: lt_products TYPE TABLE OF zproduct.

lt_products = VALUE #(
  ( id = 'P001' name = 'Laptop'   price = 999 )   " New or Update
  ( id = 'P002' name = 'Mouse'    price = 29 )    " New or Update
  ( id = 'P003' name = 'Keyboard' price = 79 )    " New or Update
).

MODIFY zproduct FROM TABLE @lt_products.

WRITE: / 'Processed rows:', sy-dbcnt.

Statement Comparison

Statement	Action	With existing key	With new key
`INSERT`	Insert	Error (sy-subrc = 4)	Insert
`UPDATE`	Update	Update	Error (sy-subrc = 4)
`DELETE`	Delete	Delete	Error (sy-subrc = 4)
`MODIFY`	Upsert	Update	Insert

Transaction Control

Database changes are not immediately persisted. They are first in a transaction buffer:

" Make changes
INSERT zcustomer FROM @ls_customer.
UPDATE zproduct SET price = 100 WHERE id = 'P001'.
DELETE FROM zlog WHERE created_at < '20240101'.

" Persist changes
COMMIT WORK.

" OR: Undo changes
" ROLLBACK WORK.

See COMMIT WORK / ROLLBACK WORK for details on transaction control.

Locking

For parallel access, you should use SAP lock objects:

" Set lock
CALL FUNCTION 'ENQUEUE_EZCUSTOMER'
  EXPORTING
    id             = ls_customer-id
  EXCEPTIONS
    foreign_lock   = 1
    system_failure = 2
    OTHERS         = 3.

IF sy-subrc = 0.
  " Make changes
  UPDATE zcustomer FROM @ls_customer.
  COMMIT WORK.

  " Release lock
  CALL FUNCTION 'DEQUEUE_EZCUSTOMER'
    EXPORTING
      id = ls_customer-id.
ELSE.
  WRITE: / 'Record is locked.'.
ENDIF.

Performance Tips

Prefer mass operations:

" Bad: Individual INSERTs in loop
LOOP AT lt_customers INTO DATA(ls_cust).
  INSERT zcustomer FROM @ls_cust.
ENDLOOP.

" Better: One INSERT for all rows
INSERT zcustomer FROM TABLE @lt_customers.

UPDATE with SET for mass changes:

" Bad: Load, change, write back
SELECT * FROM zproduct INTO TABLE @DATA(lt_products).
LOOP AT lt_products ASSIGNING FIELD-SYMBOL(<fs>).
  <fs>-status = 'ACTIVE'.
ENDLOOP.
UPDATE zproduct FROM TABLE @lt_products.

" Better: Direct UPDATE with SET
UPDATE zproduct SET status = 'ACTIVE' WHERE category = 'NEW'.

ACCEPTING DUPLICATE KEYS with INSERT: When duplicates are expected, this avoids errors and is more efficient than checking beforehand.
Commit frequency: Don’t execute COMMIT WORK after every single operation, but after logical units.

Distinction: Database Tables vs. Internal Tables

Aspect	Database Tables	Internal Tables
Statements	INSERT, UPDATE, DELETE, MODIFY	`APPEND`, `MODIFY`, `DELETE`, `INSERT`
Persistence	Permanent (after COMMIT)	Only in memory
Syntax	`INSERT <dbtab> FROM @wa`	`APPEND wa TO itab`
Transaction	COMMIT/ROLLBACK	Not applicable

Important Notes / Best Practice

Always check sy-subrc after database operations.
Use COMMIT WORK to persist changes.
Set locks for critical changes to avoid data conflicts.
Prefer mass operations (FROM TABLE) over loops with individual operations.
Be careful with DELETE without WHERE – it deletes all rows!
Only use MODIFY when you actually need “upsert” behavior.
Always test critical database operations first with ROLLBACK WORK.
Use SELECT to read and verify data before modifying.