Key없이 UPSERT

PK가 존재한다면, 해당 PK를 기반으로 UPSERT 쿼리를 짤 수 있다.

 

예를 들어 아래와 같은 테이블이 있다고 해보자.

CREATE TABLE IF NOT EXISTS TMP_TABLE (
	PK_SEQ		SERIAL		PRIMARY KEY,
	USER_ID		VARCHAR(100)	UNIQUE	NOT NULL,
	USER_NAME	VARCHAR(100)
);

 

 

만약 해당 테이블에 대한 UPSERT 쿼리를 짜야한다면 아래와 같이 짤 수 있을 것이다. (MariaDB 기준)

INSERT INTO TMP_TABLE
(PK_SEQ, USER_ID, USER_NAME)
VALUES
(1, '123okk2', 'ㅇㅁㅇ')
-- 아래가 UPSERT 부분
ON DUPLICATE KEY UPDATE
	USER_ID='123okk2',
	-- 일부러 위와 다르게 설정
	USER_NAME='ㅇㅁㅇ2';

 

위 쿼리를 두 차례 돌려보면 각각 아래와 같은 결과가 도출된다.

 

첫번째 실행

두번째 실행

 

 

UPDATE에 ㅇㅁㅇ가 아니라 ㅇㅁㅇ2를 입력했고, 이로 인해 정상적으로 UPSERT가 수행되었음을 확인했다.

 

그런데 아래 경우를 가정해보자.

PK_SEQ를 유실했을 때 UPSERT를 어떻게 하지?

 

어떠한 사정이 있어 PK_SEQ를 조회할 수 없다고 가정해보자. 그렇다면 UPSERT를 어떻게 수행해야 할까?

 

위의 테이블의 기준으로는 USER_ID가 UNIQUE이므로, PK처럼 사용이 가능할 것이다.

 

그리고 의외로 ON DUPLICATE KEY는 UNIQUE에도 적용이 된다.

DROP TABLE IF EXISTS TMP_TABLE;

CREATE TABLE IF NOT EXISTS TMP_TABLE (
	PK_SEQ		SERIAL		PRIMARY KEY,
	USER_ID		VARCHAR(100)	UNIQUE	NOT NULL,
	USER_NAME	VARCHAR(100)
);

INSERT INTO TMP_TABLE
(PK_SEQ, USER_ID, USER_NAME)
VALUES
(1, '123okk2', 'ㅇㅁㅇ')
-- 아래가 UPSERT 부분
ON DUPLICATE KEY UPDATE
	USER_ID='123okk2',
	-- 일부러 위와 다르게 설정
	USER_NAME='ㅇㅁㅇ2';
	
SELECT * FROM TMP_TABLE;

INSERT INTO TMP_TABLE
(USER_ID, USER_NAME)
VALUES
('123okk2', 'ㅇㅁㅇ')
-- 아래가 UPSERT 부분
ON DUPLICATE KEY UPDATE
	USER_ID='123okk2',
	-- 일부러 위와 다르게 설정
	USER_NAME='ㅇㅁㅇ2';
	
SELECT * FROM TMP_TABLE;

 

위 쿼리에 대한 결과는 아래와 같이 출력된다.

결과

 

 

이에 대한 챗지피티의 대답이다.

 

그런데 만약에 아래 상황도 추가되면 어떻게 할까?

USER_ID가 중복이 허용되어 UNIQUE가 아니라면?

 

실험을 위해 CREATE TABLE에서 USER_ID의 UNIQUE를 빼고 아래 쿼리를 돌려보자.

DROP TABLE IF EXISTS TMP_TABLE;

CREATE TABLE IF NOT EXISTS TMP_TABLE (
	PK_SEQ		SERIAL		PRIMARY KEY,
	USER_ID		VARCHAR(100)	NOT NULL,
	USER_NAME	VARCHAR(100)
);

INSERT INTO TMP_TABLE
(PK_SEQ, USER_ID, USER_NAME)
VALUES
(1, '123okk2', 'ㅇㅁㅇ')
-- 아래가 UPSERT 부분
ON DUPLICATE KEY UPDATE
	USER_ID='123okk2',
	-- 일부러 위와 다르게 설정
	USER_NAME='ㅇㅁㅇ2';
	
SELECT * FROM TMP_TABLE;

INSERT INTO TMP_TABLE
(USER_ID, USER_NAME)
VALUES
('123okk2', 'ㅇㅁㅇ')
-- 아래가 UPSERT 부분
ON DUPLICATE KEY UPDATE
	USER_ID='123okk2',
	-- 일부러 위와 다르게 설정
	USER_NAME='ㅇㅁㅇ2';
	
SELECT * FROM TMP_TABLE;

 

결과

 

당연히 중복을 검사할 키가 없어 ON DUPLICATE KEY UPDATE가 동작하지 않는다.

 

그러면 이 경우 어떻게 UPSERT 쿼리를 작성할까?

 

방법은 간단하다. 먼저 UPDATE 후, INSERT를 하는 것이다.

 

우선 쿼리부터 보여주자면 아래와 같이 할 수 있다.

 

UPDATE TMP_TABLE
SET
  USER_NAME='ㅇㅁㅇ2'
WHERE
  USER_ID='123okk2';
  
INSERT INTO TMP_TABLE
(USER_ID, USER_NAME)
SELECT
  -- 일부러 ㅇㅁㅇ2가 아니게 설정
  '123okk2', 'ㅇㅁㅇ'
FROM
  TMP_TABLE
WHERE
  USER_ID='123okk2'
HAVING COUNT(*) = 0;

 

쿼리는 간단하다. 우선 업데이트를 진행하는데, 만약 USER_ID가 없으면 실행되지 않을 것이다.

그 다음으로는 INSERT를 실행하는데, 이 때 VALUES에 곧바로 데이터를 집어넣지 않고 SELECT FROM 을 이용한다.

그리고 조건 절에 USER_ID와 HAVING COUNT(*) = 0;을 삽입한다. 즉, USER_ID가 '123okk2'인 컬럼이 있다면 COUNT(*)가 0이 아니라 SELECT가 실행되지 않아 INSERT 또한 실행되지 않을 것이고, COUNT(*)가 0이어야만 INSERT가 실행될 것이다.

 

이제 위 쿼리를 두 차례 돌려보자.

 

정상적으로 UPSERT가 되었음을 확인할 수 있다.