백업테이블에서 변경사항 추출하여 UPDATE문 생성하는 쿼리(feat. GPT4)

배경

현재 진행하고 있는 프로젝트에서 메뉴 항목들을 DB에서 관리한다. 

DB만 업데이트치면 바로 반영되어 좋기는 한데, 

메뉴 테이블을 공통으로 사용하기 때문에 변경사항이 큰 작업을 할 때는 부담이 크다. 

 

이번에 기존 메뉴들을 통합하고, 분리하고 이러쿵 저러쿵...한 수정건을 맡게 되었는데, 

INSERT하고 UPDATE해야하는 것들이 너무 많아서 백업테이블을 생성한 후 프로젝트가 새로 생성한 테이블을 바라보도록 하였다. 

백업테이블에서 수정한건 다른 사람들에게 영향을 주지 않으므로 마음껏 작업할 수 있다! 

 

근데 백업테이블에서 수정한 건들을 다시 원래 테이블로 옮기는 작업이 필요하다. 

메뉴테이블을 건들이는 사람이 나밖에 없어, 그냥 덮어씌워도 크게 문제가 되지는 않겠지만

만약 테이블을 누군가 수정하였는데, 내 데이터로 덮어씌운다면 기존 수정건들은 사라진다...! 

그래서 현재 테이블과 내가 만든 백업 테이블을 비교하여, 수정된 데이터만 추출 후 UPDATE하려고 한다. 

---

채찍피티에게 쿼리 짜달라고 하기

지피티에게 테이블 DDL문을 전달한 후 쿼리를 짜달라고 했다. 

 

챗지피티가 짜준 쿼리를 요약하면 다음과 같다

# UPDATE된 row 조회
SELECT *
FROM tb_sys_menu_m AS original
JOIN tb_sys_menu_m_yoojin AS backup
	ON original.menu_id = backup.menu_id
WHERE original.컬럼1 <> backup.컬럼1
	OR original.컬럼2 <> backup.컬럼2
    ...
    
# INSERT된 row 조회
SELECT *
FROM tb_sys_menu_m_yoojin AS backup
LEFT JOIN tb_sys_menu_m AS original
	ON backup.menu_id = original.menu_id
WHERE original.menu_id IS NULL;

 

쿼리를 실행하면 INSERT, UPDATE된 row 목록이 조회된다. 

---

아 근데 좀 아쉽다. UPDATE된 row들의 수정된 컬럼 목록을 조회하고 싶다. 

그래서 아래와 같이 추가적으로 명령했다. 

 

챗지피티가 짜준 쿼리를 요약하면 다음과 같다

SELECT 
    backup.menu_id,
    CONCAT(
        IF(original.컬럼1 <> backup.컬럼1, '컬럼1, ', ''),
        IF(original.컬럼2 <> backup.컬럼2, '컬럼2, ', ''),
        ...
    ) AS modified_columns
FROM tb_sys_menu_m AS original
JOIN tb_sys_menu_m_yoojin AS backup ON original.menu_id = backup.menu_id
WHERE original.컬럼1 <> backup.컬럼1
	OR original.컬럼2 <> backup.컬럼2
    ...

 

위에 쿼리를 실행하면 menu_id(PK)와 수정된 컬럼명 목록이 조회된다. 

---

좋다. 근데 나는 업데이트문을 추출하고 싶다! 

지피티에게 시켰는데, 못알아듣는거 같아 직접 쿼리를 작성하였다. 

SELECT 
    backup.menu_id,
    backup.menu_dscr, 
    CONCAT(
    	'UPDATE tb_sys_menu_m SET ',
    	TRIM(TRAILING ', ' FROM CONCAT( 
	        IF(original.컬럼1 <> backup.컬럼1, CONCAT('컬럼1 = \'', backup.컬럼1, '\', '), ''),
	        IF(original.컬럼2 <> backup.컬럼2, CONCAT('컬럼2 = ', backup.컬럼2, ', '), ''),
            ...
        )),
        ' WHERE menu_id = \'',
        backup.menu_id, 
        '\';'
    ) AS update_query
FROM tb_sys_menu_m AS original
JOIN tb_sys_menu_m_yoojin AS backup ON original.menu_id = backup.menu_id
WHERE original.컬럼1 <> backup.컬럼1
	OR original.컬럼2 <> backup.컬럼2
    ...

menu_id와 컬럼1의 데이터타입이 문자이기 때문에 앞뒤에 작은따옴표를 붙여줬다. 

TRIM함수를 사용해 SET문 마지막에 붙은 콤마를 삭제해줬다. 

---

실행시켜보자. 

좋다. 아주 만족스럽다!!

update_query만 복사하여 그대로 실행하면 내가 원하는대로 동작할거다! 

---

여기에 추가적으로 함께 UPDATE해줘야하는 컬럼이 있어서, 

해당 컬럼을 UPDATE하는 부분도 추가하였다. 

  SELECT 
    backup.menu_id,
    backup.menu_dscr, 
    CONCAT(
    	'UPDATE tb_sys_menu_m SET ',
    	CONCAT( 
            IF(original.컬럼1 <> backup.컬럼1, CONCAT('컬럼1 = \'', backup.컬럼1, '\', '), ''),
	        IF(original.컬럼2 <> backup.컬럼2, CONCAT('컬럼2 = ', backup.컬럼2, ', '), ''),
            ...
        ),
        'data_upd_dttm = NOW() '
        'WHERE menu_id = \'',
        backup.menu_id, 
        '\';'
    ) AS update_query
FROM tb_sys_menu_m AS original
JOIN tb_sys_menu_m_yoojin AS backup ON original.menu_id = backup.menu_id
WHERE original.컬럼1 <> backup.컬럼1
	OR original.컬럼2 <> backup.컬럼2
    ...

data_upd_dttm을 현재일시로 세팅하는 부분이 추가되었고, 

맨 마지막 콤마를 제거하는 TRIM함수는 더이상 필요가 없어 삭제하였다. 

 

완벽하다. 내가 꿈꿨던 쿼리다. 

근데 단점이 한가지 있다. 테이블마다 컬럼 목록이 다르기 때문에 각자 쿼리를 생성해야한다는 것... 

---

이런건 GPT에게 짬때리면 된다. 

내가 만든 뼈대 쿼리를 보낸 후 앞으로 DDL을 보내면 변환해달라고 명령한다

---

바로 DDL문을 보내 테스트해보았다. 

 

기가맥힌다. 완벽하다 !!!