14. [SQL] MySQL INSERT ... ON DUPLICATE KEY UPDATE Statement - 있으면 업데이트하고 없으면 새로 INSERT 하는 구문

만약 고객들의 로그 데이터를 활용하기 위해 통계 데이터를 꾸준히 업데이트 해야하는데 매번 지웠다가 다시 insert 할수 없는 크기라면 ? 

• 아물론 매번 delete 했다가 다시 집어넣을수도 있다. 하지만 번거롭고 위험하다. 
•  UPDATE를 하면 되는것 아닌가 ?  아래처럼

UPDATE 테이블이름

SET 필드이름1=데이터값1, 필드이름2=데이터값2, ...

WHERE 필드이름=데이터값

• 이렇게 하면 알맞은 값을 수정시켜 주기는 한다. 하지만 원하는건 테이블 전체를 스캔한 뒤에 알맞게 업데이트 해야하면 업데이트 하고, INSERT 해야하면 INSERT하는 그런 구문을 희망하는건데... 
• 이건 그냥 데이터 수정 아닌가 ??? 

 

ON DUPLICATE KEY UPDATE 사용하기

• MySQL에서는 대량의 파일을 등록 및 수정하는경우 기존 데이터는 UPDATE하고 신규 데이터는 INSERT 시키는 ON DUPLICATE KEY UPDATE 기능을 제공한다.

MySQL4.1 버전부터 지원
Oracle의 Merge Into와 유사
사용하려면 PK 나 유니크 키가 필요
​
데이터를 테이블에 insert 시 대상 테이블에 해당 키 데이터가 없으면 insert 문 실행 , 해당 키 데이터가 테이블에 존재하면 다른 컬럼 값들을 update 함
​
중복 insert 를 막으려면 중복 방지 할 컬럼이 PK나 유니크 키여야 한다고 함
암튼 pk로 중복을 거른다는 소리니까

기존에 이미 있는 테이블에 pk 추가하거나 유니크 키 추가하려면
# PRIMARY 추가 ALTER TABLE person ADD PRIMARY KEY (name)
# UNIQUE INDEX를 추가 ALTER TABLE person ADD UNIQUE INDEX (name)

출처 : https://m.blog.naver.com/vivacarla/222069639578

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

여기 공식문서에 나와있는데 기본 코드는 아래와 같다. 

 

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

 

INSERT ,  ON DUPLICATE KEY UPDATE 구문을 따로 보아야 하고 

만약 SELECT 구문을 활용해서 테이블에 어떠한 조건을 걸고 작업을 진행하려면 아래와 같이 구문을 쓰면 된다.

Insert INTO [insert할테이블명] (
insert할컬럼1, insert할컬럼2, insert할컬럼3, ...
)
SELECT select해서값가져올컬럼1
	, select해서값가져올컬럼2
    , select해서값가져올컬럼3, ...
FROM [select해서값가져올테이블] t 
# 여기다 조건 
WHERE DelYN = 'N'
# 여기서 부터는 업데이트
ON DUPLICATE KEY UPDATE 
	업데이트할컬럼1= select해서값가져올컬럼1, 
    업데이트할컬럼2= select해서값가져올컬럼2,
    ...

 

근데 이렇게 하면 전부 테이블 스캔해서 업데이트 하니까 

만약 고객 행동 통계 테이블을 만들기 위해서 고객들의 방문총합을 구하면 ?

SELECT 
cust_seq
,SUM(DISTINCT  date(reg_dttm )) as sum_visit_date
from ...
group by ... (생략)

 이렇게 구할수 있고 

sum_visit_date = sum_visit_date + a.sum_visit_date 이런식으로 UPDATE 구문에 조건을 두면 된다. 

ON DUPLICATE KEY UPDATE 
	sum_visit_date = sum_visit_date + a.sum_visit_date, 
    업데이트할컬럼2= select해서값가져올컬럼2,
    ...

아 물론 cust_seq에는 업데이트 할게 없으므로 굳이 업데이트 구문에 넣을 필요가 없다.

---

 

찾아보니 Maria DB에도 비슷한게 있다.

https://mariadb.com/kb/en/insert-on-duplicate-key-update/

INSERT ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

UPDATE