[Open SQL & Native SQL] INSERT & UPDATE & DELETE & MODIFY

Table data 확인방법 => T-code: SE11 | SE16

 

[INSERT 구문]

Table에 데이터를 삽입하는 구문œ

1) Single line INSERT

• INSERT INTO <target> VALUES <wa>
• INSERT <target> FROM <wa>

DATA : gs_spfli TYPE spfli.

* 1번 형식
gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0004'.
gs_spfli-countryfr = 'KO'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'Jeju'.

INSERT INTO spfli VALUES gs_spfli.

* 2번 형식
gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0005'.
gs_spfli-countryfr = 'KO'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'Bangkok'.

INSERT spfli FROM gs_spfli.

2) Several line INSERT

• INSERT <target> FROM TABLE <itab> [ACCEPTING DUPLICATE KEYS]
• ACCEPTING DUPLICATE KEYS: 같은 key값을 INSERT할때 발생하는 dump error방지.
• INSERT구문 실패시 sy-subrc값 4 반환.

REPORT ZA03_16.

DATA : gt_spfli  TYPE TABLE OF spfli,
       gs_spfli  TYPE spfli.

gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0001'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'Beijing'.
APPEND gs_spfli TO gt_spfli.

gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0002'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'Bangalore'.
APPEND gs_spfli TO gt_spfli.

INSERT spfli FROM TABLE gt_spfli ACCEPTING DUPLICATE KEYS.

 

 

[UPDATE 구문]

1) Single line UPDATE.

• UPDATE <target> FROM <wa>

2) Several line UPDATE.

• UPDATE <target> FROM TABLE <itab>

REPORT ZA03_17.

DATA : gs_spfli TYPE spfli,
       gt_spfli TYPE TABLE OF spfli.
====================================
* Single line UPDATE
gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0001'.
gs_spfli-countryfr = 'KO'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'JEJU'.
UPDATE spfli FROM gs_spfli.
====================================
* Several line UPDATE
gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0002'.
gs_spfli-countryfr = 'KO'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'JEJU'.
APPEND gs_spfli TO gt_spfli.

gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0003'.
gs_spfli-countryfr = 'KO'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'JEJU'.
APPEND gs_spfli TO gt_spfli.

gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0004'.
gs_spfli-countryfr = 'KO'.
gs_spfli-cityfrom = 'Seoul'.
gs_spfli-cityto = 'JEJU'.
APPEND gs_spfli TO gt_spfli.

UPDATE spfli FROM TABLE gt_spfli.
====================================
* Several line update (SET 구문)
UPDATE spfli
SET cityto = 'JEJU'
WHERE carrid = 'KO'
  AND connid = '0005'.

 

[DELETE 구문]

1) Single line UPDATE

• DELETE <target> FROM <wa>

2) Several line UPDATE

• DELETE FROM <target> WHERE <cond>.

REPORT ZA03_18.

DATA : gs_spfli TYPE spfli.

* Single line DELETE
gs_spfli-carrid = 'KO'.
gs_spfli-connid = '0001'.
DELETE spfli FROM gs_spfli.

* Several line DELETE
DELETE FROM spfli WHERE carrid = 'KO'.

 

[MODIFY 구문]

Target 테이블에 key값을 갖는 데이터가 존재하면 테이블을 UPDATE하고, key값을 갖는 데이터가 존재하지 않으면 테이블에 INSERT하는 구문

1) Single line MODIFY

• MODIFY <target> FROM <wa>

2) Several line MODIFY

• MODIFY  <target> FROM TABLE <itab>

• REPORT ZA03_19.
  
  DATA : gs_spfli TYPE spfli,
         gt_spfli TYPE TABLE OF spfli.
  
  * Single line MODIFY
  gs_spfli-carrid = 'KO'.
  gs_spfli-connid = '0001'.
  gs_spfli-cityfrom = 'Seoul'.
  gs_spfli-cityto = 'Osaka'.
  MODIFY spfli FROM gs_spfli.
  
  * Several line MODIFY
  gs_spfli-carrid = 'KO'.
  gs_spfli-connid = '0002'.
  gs_spfli-cityfrom = 'Seoul'.
  gs_spfli-cityto = 'Tokyo'.
  APPEND gs_spfli TO gt_spfli.
  
  gs_spfli-carrid = 'KO'.
  gs_spfli-connid = '0003'.
  gs_spfli-cityfrom = 'Seoul'.
  gs_spfli-cityto = 'Kyoto'.
  APPEND gs_spfli TO gt_spfli.
  
  MODIFY spfli FROM TABLE gt_spfli.