SQL MODIFY

Posted by Bryan

---

Syntax

MODIFY <target_database_tab> <databae_tab_lines>

This is a very useful function for us to update or insert data.

This MODIFY will either UPDATE or INSERT data to the table. It will base on the primary key to determine the operation of UPDATE or INSERT. When the keys are exists, UPDATE will use to update the existing data. But when the keys is not exists, it will switch to INSERT operation and create a new record.

The syntax is similar with UPDATE and DELETE.

Example 1: MODIFY a single record

TABLES KNA1.
DATA vline TYPE KNA1.
vline-KUNNR = ‘000001’.
vline-NAME1 = ‘Bryan’.
MODIFY KNA1 FROM vline.

If the record with KUNNR=‘000001’ is exists and NAME1 = ‘Bryan Chong’, this operation will update the NAME1 to ‘Bryan’.

If the record is not exists, then a new record with KUNNR= ‘000001’ and NAME1 = ‘Bryan’ will be created.

Example 2: MODIFY multiple records

DATA: myTable TYPE HASHED TABLE OF KNA1
WITH UNIQUE KEY KUNNR,
tabLine LIKE LINE OF myTable.

tabLine-KUNNR = ‘000001’.
tabLine-NAME1 = ‘Bryan’.
INSERT tabLine INTO TABLE myTable.

tabLine-KUNNR = ‘000009’.
tabLine-NAME1 = ‘Jack’.
tabLine-ORT01 = ‘Kuala Lumpur’.
INSERT tabLine INTO TABLE myTable.

MODIFY KNA1 FROM myTable.