[MySQL]Insert, Update, Delete Query Generator

정보시스템을 개발하기 위해서는 데이터베이스 기반으로 개발을 해야 할 것입니다.

필자는 과거 프로젝트를 진행하면서 데이터베이스의 테이블 생성 또는 변경 시 테이블의 Insert, Update, Delete 쿼리를 지속적으로 작성했습니다.

아마 여러 개발자님들도 DBA(규모가 있는 프로젝트 인 경우) 또는 PM/PL이 추가/변경한 테이블에 대한 Insert, Update, Delete 쿼리를 필자와 마찬가지고 작성하셨을 것 이라고 생각합니다.

이 부분이 단순작업이고 귀찮은 작업입니다.

단순한 테이블 같은 경우 직접 작성하시면 되지만, 복잡한? 컬럼이 많은 테이블의 Insert/Update/Delete 쿼리를 작성하실 때는 최소 5분 정도의 시간이 걸릴 것 입니다.

이런 시간도 아깝다고 생각하여, 단순 노가다(?) 같은 작업을 자동화 하기 위해 Insert/Update/Delete 쿼리를 작성해주는 쿼리를 작성했습니다.

아래의 쿼리를 복사하셔서 &TBLNM을 테이블명으로 치환하셔서 실행하시면 MyBatis Tag와 쿼리를 작성하여 줍니다.

여러분의 개발시간을 단 5분이라도 단축해 드리고자 이렇게 공유하여 드립니다.

&BEAN_NM은 VolueObject Class Name으로 치환하시면 됩니다.

SELECT CONCAT('<!-- ', 'INSERT QUERY -->') AS QUERY UNION ALL
SELECT CONCAT('<insert id="insert" parameterClass="', IFNULL('&BEAN_NM', ''), '">') UNION ALL
SELECT CONCAT('INSERT INTO ', UPPER('&TBLNM'), ' (') UNION ALL
SELECT LEFT(COLUMN_NAMES, LENGTH(COLUMN_NAMES) - 2) AS COLUMN_NAMES
  FROM (
       SELECT CONCAT(
              IFNULL(CONCAT(COLUMN01, ', '), ''), IFNULL(CONCAT(COLUMN02, ', '), ''), IFNULL(CONCAT(COLUMN03, ', '), ''), IFNULL(CONCAT(COLUMN04, ', '), ''), IFNULL(CONCAT(COLUMN05, ', '), ''), IFNULL(CONCAT(COLUMN06, ', '), ''), IFNULL(CONCAT(COLUMN07, ', '), ''), IFNULL(CONCAT(COLUMN08, ', '), ''), IFNULL(CONCAT(COLUMN09, ', '), ''), IFNULL(CONCAT(COLUMN10, ', '), ''), 
              IFNULL(CONCAT(COLUMN11, ', '), ''), IFNULL(CONCAT(COLUMN12, ', '), ''), IFNULL(CONCAT(COLUMN13, ', '), ''), IFNULL(CONCAT(COLUMN14, ', '), ''), IFNULL(CONCAT(COLUMN15, ', '), ''), IFNULL(CONCAT(COLUMN16, ', '), ''), IFNULL(CONCAT(COLUMN17, ', '), ''), IFNULL(CONCAT(COLUMN18, ', '), ''), IFNULL(CONCAT(COLUMN19, ', '), ''), IFNULL(CONCAT(COLUMN20, ', '), ''), 
              IFNULL(CONCAT(COLUMN21, ', '), ''), IFNULL(CONCAT(COLUMN22, ', '), ''), IFNULL(CONCAT(COLUMN23, ', '), ''), IFNULL(CONCAT(COLUMN24, ', '), ''), IFNULL(CONCAT(COLUMN25, ', '), ''), IFNULL(CONCAT(COLUMN26, ', '), ''), IFNULL(CONCAT(COLUMN27, ', '), ''), IFNULL(CONCAT(COLUMN28, ', '), ''), IFNULL(CONCAT(COLUMN29, ', '), ''), IFNULL(CONCAT(COLUMN30, ', '), ''), 
              IFNULL(CONCAT(COLUMN31, ', '), ''), IFNULL(CONCAT(COLUMN32, ', '), ''), IFNULL(CONCAT(COLUMN33, ', '), ''), IFNULL(CONCAT(COLUMN34, ', '), ''), IFNULL(CONCAT(COLUMN35, ', '), ''), IFNULL(CONCAT(COLUMN36, ', '), ''), IFNULL(CONCAT(COLUMN37, ', '), ''), IFNULL(CONCAT(COLUMN38, ', '), ''), IFNULL(CONCAT(COLUMN39, ', '), ''), IFNULL(CONCAT(COLUMN40, ', '), ''), 
              IFNULL(CONCAT(COLUMN41, ', '), ''), IFNULL(CONCAT(COLUMN42, ', '), ''), IFNULL(CONCAT(COLUMN43, ', '), ''), IFNULL(CONCAT(COLUMN44, ', '), ''), IFNULL(CONCAT(COLUMN45, ', '), ''), IFNULL(CONCAT(COLUMN46, ', '), ''), IFNULL(CONCAT(COLUMN47, ', '), ''), IFNULL(CONCAT(COLUMN48, ', '), ''), IFNULL(CONCAT(COLUMN49, ', '), ''), IFNULL(CONCAT(COLUMN50, ', '), '')
              ) AS COLUMN_NAMES
         FROM (
                SELECT MAX(COLUMN01) AS COLUMN01, MAX(COLUMN02) AS COLUMN02, MAX(COLUMN03) AS COLUMN03, MAX(COLUMN04) AS COLUMN04, MAX(COLUMN05) AS COLUMN05, MAX(COLUMN06) AS COLUMN06, MAX(COLUMN07) AS COLUMN07, MAX(COLUMN08) AS COLUMN08, MAX(COLUMN09) AS COLUMN09, MAX(COLUMN10) AS COLUMN10,
                       MAX(COLUMN11) AS COLUMN11, MAX(COLUMN12) AS COLUMN12, MAX(COLUMN13) AS COLUMN13, MAX(COLUMN14) AS COLUMN14, MAX(COLUMN15) AS COLUMN15, MAX(COLUMN16) AS COLUMN16, MAX(COLUMN17) AS COLUMN17, MAX(COLUMN18) AS COLUMN18, MAX(COLUMN19) AS COLUMN19, MAX(COLUMN20) AS COLUMN20,
                       MAX(COLUMN21) AS COLUMN21, MAX(COLUMN22) AS COLUMN22, MAX(COLUMN23) AS COLUMN23, MAX(COLUMN24) AS COLUMN24, MAX(COLUMN25) AS COLUMN25, MAX(COLUMN26) AS COLUMN26, MAX(COLUMN27) AS COLUMN27, MAX(COLUMN28) AS COLUMN28, MAX(COLUMN29) AS COLUMN29, MAX(COLUMN30) AS COLUMN30,
                       MAX(COLUMN31) AS COLUMN31, MAX(COLUMN32) AS COLUMN32, MAX(COLUMN33) AS COLUMN33, MAX(COLUMN34) AS COLUMN34, MAX(COLUMN35) AS COLUMN35, MAX(COLUMN36) AS COLUMN36, MAX(COLUMN37) AS COLUMN37, MAX(COLUMN38) AS COLUMN38, MAX(COLUMN39) AS COLUMN39, MAX(COLUMN40) AS COLUMN40,
                       MAX(COLUMN41) AS COLUMN41, MAX(COLUMN42) AS COLUMN42, MAX(COLUMN43) AS COLUMN43, MAX(COLUMN44) AS COLUMN44, MAX(COLUMN45) AS COLUMN45, MAX(COLUMN46) AS COLUMN46, MAX(COLUMN47) AS COLUMN47, MAX(COLUMN48) AS COLUMN48, MAX(COLUMN49) AS COLUMN49, MAX(COLUMN50) AS COLUMN50
                  FROM (
                         SELECT ORDINAL_POSITION,
                                IF(ORDINAL_POSITION=1, COLUMN_NAME, NULL) AS COLUMN01, IF(ORDINAL_POSITION=2, COLUMN_NAME, NULL) AS COLUMN02, IF(ORDINAL_POSITION=3, COLUMN_NAME, NULL) AS COLUMN03, IF(ORDINAL_POSITION=4, COLUMN_NAME, NULL) AS COLUMN04, IF(ORDINAL_POSITION=5, COLUMN_NAME, NULL) AS COLUMN05, IF(ORDINAL_POSITION=6, COLUMN_NAME, NULL) AS COLUMN06, IF(ORDINAL_POSITION=7, COLUMN_NAME, NULL) AS COLUMN07, IF(ORDINAL_POSITION=8, COLUMN_NAME, NULL) AS COLUMN08, IF(ORDINAL_POSITION=9, COLUMN_NAME, NULL) AS COLUMN09, IF(ORDINAL_POSITION=10, COLUMN_NAME, NULL) AS COLUMN10,
                                IF(ORDINAL_POSITION=11, COLUMN_NAME, NULL) AS COLUMN11, IF(ORDINAL_POSITION=12, COLUMN_NAME, NULL) AS COLUMN12, IF(ORDINAL_POSITION=13, COLUMN_NAME, NULL) AS COLUMN13, IF(ORDINAL_POSITION=14, COLUMN_NAME, NULL) AS COLUMN14, IF(ORDINAL_POSITION=15, COLUMN_NAME, NULL) AS COLUMN15, IF(ORDINAL_POSITION=16, COLUMN_NAME, NULL) AS COLUMN16, IF(ORDINAL_POSITION=17, COLUMN_NAME, NULL) AS COLUMN17, IF(ORDINAL_POSITION=18, COLUMN_NAME, NULL) AS COLUMN18, IF(ORDINAL_POSITION=19, COLUMN_NAME, NULL) AS COLUMN19, IF(ORDINAL_POSITION=20, COLUMN_NAME, NULL) AS COLUMN20,
                                IF(ORDINAL_POSITION=21, COLUMN_NAME, NULL) AS COLUMN21, IF(ORDINAL_POSITION=22, COLUMN_NAME, NULL) AS COLUMN22, IF(ORDINAL_POSITION=23, COLUMN_NAME, NULL) AS COLUMN23, IF(ORDINAL_POSITION=24, COLUMN_NAME, NULL) AS COLUMN24, IF(ORDINAL_POSITION=25, COLUMN_NAME, NULL) AS COLUMN25, IF(ORDINAL_POSITION=26, COLUMN_NAME, NULL) AS COLUMN26, IF(ORDINAL_POSITION=27, COLUMN_NAME, NULL) AS COLUMN27, IF(ORDINAL_POSITION=28, COLUMN_NAME, NULL) AS COLUMN28, IF(ORDINAL_POSITION=29, COLUMN_NAME, NULL) AS COLUMN29, IF(ORDINAL_POSITION=30, COLUMN_NAME, NULL) AS COLUMN30,
                                IF(ORDINAL_POSITION=31, COLUMN_NAME, NULL) AS COLUMN31, IF(ORDINAL_POSITION=32, COLUMN_NAME, NULL) AS COLUMN32, IF(ORDINAL_POSITION=33, COLUMN_NAME, NULL) AS COLUMN33, IF(ORDINAL_POSITION=34, COLUMN_NAME, NULL) AS COLUMN34, IF(ORDINAL_POSITION=35, COLUMN_NAME, NULL) AS COLUMN35, IF(ORDINAL_POSITION=36, COLUMN_NAME, NULL) AS COLUMN36, IF(ORDINAL_POSITION=37, COLUMN_NAME, NULL) AS COLUMN37, IF(ORDINAL_POSITION=38, COLUMN_NAME, NULL) AS COLUMN38, IF(ORDINAL_POSITION=39, COLUMN_NAME, NULL) AS COLUMN39, IF(ORDINAL_POSITION=40, COLUMN_NAME, NULL) AS COLUMN40,
                                IF(ORDINAL_POSITION=41, COLUMN_NAME, NULL) AS COLUMN41, IF(ORDINAL_POSITION=42, COLUMN_NAME, NULL) AS COLUMN42, IF(ORDINAL_POSITION=43, COLUMN_NAME, NULL) AS COLUMN43, IF(ORDINAL_POSITION=44, COLUMN_NAME, NULL) AS COLUMN44, IF(ORDINAL_POSITION=45, COLUMN_NAME, NULL) AS COLUMN45, IF(ORDINAL_POSITION=46, COLUMN_NAME, NULL) AS COLUMN46, IF(ORDINAL_POSITION=47, COLUMN_NAME, NULL) AS COLUMN47, IF(ORDINAL_POSITION=48, COLUMN_NAME, NULL) AS COLUMN48, IF(ORDINAL_POSITION=49, COLUMN_NAME, NULL) AS COLUMN49, IF(ORDINAL_POSITION=50, COLUMN_NAME, NULL) AS COLUMN50
                           FROM INFORMATION_SCHEMA.COLUMNS
                          WHERE UPPER(TABLE_NAME) = UPPER('&TBLNM')
                            AND COLUMN_NAME NOT IN ('UPDATE_USER', 'UPDATE_DATE')
                       ) AS A
                ORDER BY ORDINAL_POSITION
              ) AS B
       ) AS C
UNION ALL
SELECT ') VALUES (' UNION ALL
SELECT LEFT(COLUMN_NAMES, LENGTH(COLUMN_NAMES) - 2) AS COLUMN_NAMES
  FROM (
       SELECT CONCAT(
              IFNULL(CONCAT('#{', LOWER(COLUMN01), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN02), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN03), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN04), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN05), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN06), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN07), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN08), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN09), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN10), '}, '), ''), 
              IFNULL(CONCAT('#{', LOWER(COLUMN11), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN12), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN13), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN14), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN15), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN16), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN17), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN18), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN19), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN20), '}, '), ''), 
              IFNULL(CONCAT('#{', LOWER(COLUMN21), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN22), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN23), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN24), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN25), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN26), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN27), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN28), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN29), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN30), '}, '), ''), 
              IFNULL(CONCAT('#{', LOWER(COLUMN31), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN32), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN33), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN34), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN35), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN36), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN37), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN38), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN39), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN40), '}, '), ''), 
              IFNULL(CONCAT('#{', LOWER(COLUMN41), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN42), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN43), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN44), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN45), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN46), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN47), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN48), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN49), '}, '), ''), IFNULL(CONCAT('#{', LOWER(COLUMN50), '}, '), '')
              ) AS COLUMN_NAMES
         FROM (
                SELECT MAX(COLUMN01) AS COLUMN01, MAX(COLUMN02) AS COLUMN02, MAX(COLUMN03) AS COLUMN03, MAX(COLUMN04) AS COLUMN04, MAX(COLUMN05) AS COLUMN05, MAX(COLUMN06) AS COLUMN06, MAX(COLUMN07) AS COLUMN07, MAX(COLUMN08) AS COLUMN08, MAX(COLUMN09) AS COLUMN09, MAX(COLUMN10) AS COLUMN10,
                       MAX(COLUMN11) AS COLUMN11, MAX(COLUMN12) AS COLUMN12, MAX(COLUMN13) AS COLUMN13, MAX(COLUMN14) AS COLUMN14, MAX(COLUMN15) AS COLUMN15, MAX(COLUMN16) AS COLUMN16, MAX(COLUMN17) AS COLUMN17, MAX(COLUMN18) AS COLUMN18, MAX(COLUMN19) AS COLUMN19, MAX(COLUMN20) AS COLUMN20,
                       MAX(COLUMN21) AS COLUMN21, MAX(COLUMN22) AS COLUMN22, MAX(COLUMN23) AS COLUMN23, MAX(COLUMN24) AS COLUMN24, MAX(COLUMN25) AS COLUMN25, MAX(COLUMN26) AS COLUMN26, MAX(COLUMN27) AS COLUMN27, MAX(COLUMN28) AS COLUMN28, MAX(COLUMN29) AS COLUMN29, MAX(COLUMN30) AS COLUMN30,
                       MAX(COLUMN31) AS COLUMN31, MAX(COLUMN32) AS COLUMN32, MAX(COLUMN33) AS COLUMN33, MAX(COLUMN34) AS COLUMN34, MAX(COLUMN35) AS COLUMN35, MAX(COLUMN36) AS COLUMN36, MAX(COLUMN37) AS COLUMN37, MAX(COLUMN38) AS COLUMN38, MAX(COLUMN39) AS COLUMN39, MAX(COLUMN40) AS COLUMN40,
                       MAX(COLUMN41) AS COLUMN41, MAX(COLUMN42) AS COLUMN42, MAX(COLUMN43) AS COLUMN43, MAX(COLUMN44) AS COLUMN44, MAX(COLUMN45) AS COLUMN45, MAX(COLUMN46) AS COLUMN46, MAX(COLUMN47) AS COLUMN47, MAX(COLUMN48) AS COLUMN48, MAX(COLUMN49) AS COLUMN49, MAX(COLUMN50) AS COLUMN50
                  FROM (
                         SELECT ORDINAL_POSITION,
                                IF(ORDINAL_POSITION=1, COLUMN_NAME, NULL) AS COLUMN01, IF(ORDINAL_POSITION=2, COLUMN_NAME, NULL) AS COLUMN02, IF(ORDINAL_POSITION=3, COLUMN_NAME, NULL) AS COLUMN03, IF(ORDINAL_POSITION=4, COLUMN_NAME, NULL) AS COLUMN04, IF(ORDINAL_POSITION=5, COLUMN_NAME, NULL) AS COLUMN05, IF(ORDINAL_POSITION=6, COLUMN_NAME, NULL) AS COLUMN06, IF(ORDINAL_POSITION=7, COLUMN_NAME, NULL) AS COLUMN07, IF(ORDINAL_POSITION=8, COLUMN_NAME, NULL) AS COLUMN08, IF(ORDINAL_POSITION=9, COLUMN_NAME, NULL) AS COLUMN09, IF(ORDINAL_POSITION=10, COLUMN_NAME, NULL) AS COLUMN10,
                                IF(ORDINAL_POSITION=11, COLUMN_NAME, NULL) AS COLUMN11, IF(ORDINAL_POSITION=12, COLUMN_NAME, NULL) AS COLUMN12, IF(ORDINAL_POSITION=13, COLUMN_NAME, NULL) AS COLUMN13, IF(ORDINAL_POSITION=14, COLUMN_NAME, NULL) AS COLUMN14, IF(ORDINAL_POSITION=15, COLUMN_NAME, NULL) AS COLUMN15, IF(ORDINAL_POSITION=16, COLUMN_NAME, NULL) AS COLUMN16, IF(ORDINAL_POSITION=17, COLUMN_NAME, NULL) AS COLUMN17, IF(ORDINAL_POSITION=18, COLUMN_NAME, NULL) AS COLUMN18, IF(ORDINAL_POSITION=19, COLUMN_NAME, NULL) AS COLUMN19, IF(ORDINAL_POSITION=20, COLUMN_NAME, NULL) AS COLUMN20,
                                IF(ORDINAL_POSITION=21, COLUMN_NAME, NULL) AS COLUMN21, IF(ORDINAL_POSITION=22, COLUMN_NAME, NULL) AS COLUMN22, IF(ORDINAL_POSITION=23, COLUMN_NAME, NULL) AS COLUMN23, IF(ORDINAL_POSITION=24, COLUMN_NAME, NULL) AS COLUMN24, IF(ORDINAL_POSITION=25, COLUMN_NAME, NULL) AS COLUMN25, IF(ORDINAL_POSITION=26, COLUMN_NAME, NULL) AS COLUMN26, IF(ORDINAL_POSITION=27, COLUMN_NAME, NULL) AS COLUMN27, IF(ORDINAL_POSITION=28, COLUMN_NAME, NULL) AS COLUMN28, IF(ORDINAL_POSITION=29, COLUMN_NAME, NULL) AS COLUMN29, IF(ORDINAL_POSITION=30, COLUMN_NAME, NULL) AS COLUMN30,
                                IF(ORDINAL_POSITION=31, COLUMN_NAME, NULL) AS COLUMN31, IF(ORDINAL_POSITION=32, COLUMN_NAME, NULL) AS COLUMN32, IF(ORDINAL_POSITION=33, COLUMN_NAME, NULL) AS COLUMN33, IF(ORDINAL_POSITION=34, COLUMN_NAME, NULL) AS COLUMN34, IF(ORDINAL_POSITION=35, COLUMN_NAME, NULL) AS COLUMN35, IF(ORDINAL_POSITION=36, COLUMN_NAME, NULL) AS COLUMN36, IF(ORDINAL_POSITION=37, COLUMN_NAME, NULL) AS COLUMN37, IF(ORDINAL_POSITION=38, COLUMN_NAME, NULL) AS COLUMN38, IF(ORDINAL_POSITION=39, COLUMN_NAME, NULL) AS COLUMN39, IF(ORDINAL_POSITION=40, COLUMN_NAME, NULL) AS COLUMN40,
                                IF(ORDINAL_POSITION=41, COLUMN_NAME, NULL) AS COLUMN41, IF(ORDINAL_POSITION=42, COLUMN_NAME, NULL) AS COLUMN42, IF(ORDINAL_POSITION=43, COLUMN_NAME, NULL) AS COLUMN43, IF(ORDINAL_POSITION=44, COLUMN_NAME, NULL) AS COLUMN44, IF(ORDINAL_POSITION=45, COLUMN_NAME, NULL) AS COLUMN45, IF(ORDINAL_POSITION=46, COLUMN_NAME, NULL) AS COLUMN46, IF(ORDINAL_POSITION=47, COLUMN_NAME, NULL) AS COLUMN47, IF(ORDINAL_POSITION=48, COLUMN_NAME, NULL) AS COLUMN48, IF(ORDINAL_POSITION=49, COLUMN_NAME, NULL) AS COLUMN49, IF(ORDINAL_POSITION=50, COLUMN_NAME, NULL) AS COLUMN50
                           FROM INFORMATION_SCHEMA.COLUMNS
                          WHERE UPPER(TABLE_NAME) = UPPER('&TBLNM')
                            AND COLUMN_NAME NOT IN ('UPDATE_USER', 'UPDATE_DATE')
                       ) AS A
                ORDER BY ORDINAL_POSITION
              ) AS B
       ) AS C
UNION ALL
SELECT ')' UNION ALL
SELECT '</insert>' UNION ALL
SELECT '' UNION ALL
SELECT CONCAT('<!-- ', 'UPDATE QUERY -->')  UNION ALL
SELECT CONCAT('<update id="update" parameterClass="', IFNULL('&BEAN_NM', ''), '">') UNION ALL
SELECT CONCAT('UPDATE ', '&TBLNM', ' SET') UNION ALL
SELECT CONCAT('       ', COLUMN_NAME, ' = #{', LOWER(COLUMN_NAME), '}, ')
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE UPPER(TABLE_NAME) = '&TBLNM'
   AND COLUMN_KEY = ''
   AND COLUMN_NAME NOT IN ('INIT_USER', 'INIT_DATE')
UNION ALL
SELECT ' WHERE' UNION ALL
SELECT CONCAT('   AND ', COLUMN_NAME, ' = #{', LOWER(COLUMN_NAME), '}, ')
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE UPPER(TABLE_NAME) = '&TBLNM'
   AND COLUMN_KEY = 'PRI'
   AND COLUMN_NAME NOT IN ('INIT_USER', 'INIT_DATE')
UNION ALL
SELECT '</update>' UNION ALL
SELECT '' UNION ALL
SELECT CONCAT('<!-- ', 'DELETE QUERY -->')  UNION ALL
SELECT CONCAT('<delete id="delete" parameterClass="', IFNULL('&BEAN_NM', ''), '">') UNION ALL
SELECT CONCAT('DELETE FROM ', UPPER('&TBLNM')) UNION ALL
SELECT ' WHERE' UNION ALL
SELECT CONCAT('   AND ', COLUMN_NAME, ' = #{', LOWER(COLUMN_NAME), '}, ')
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE UPPER(TABLE_NAME) = '&TBLNM'
   AND COLUMN_KEY = 'PRI'
   AND COLUMN_NAME NOT IN ('INIT_USER', 'INIT_DATE')
UNION ALL
SELECT '</delete>'

[mysql]insert_update_delete query generator.txt