SQL - UPSERT 관련 기술 비교 Oracle-Mysql-YugaByte-SingleStore

UPSERT 기능은 기존 레코드가 존재할 경우 업데이트하고, 없을 경우 새로 삽입하는 데이터베이스 연산입니다. 각 데이터베이스마다 구현 방식이 다르며, 다음은 Oracle, MySQL, YugaByte의 문법과 예시를 비교한 내용입니다.

데이터베이스별 UPSERT 구현 비교

데이터베이스	문법 유형	upsert 예시	참고 사항
Oracle	UPSERT 명령어 사용	MERGE INTO employees e USING (SELECT 101 AS id, 'Jane Doe' AS name FROM dual) src ON (e.id = src.id) WHEN MATCHED THEN   UPDATE SET e.name = src.name WHEN NOT MATCHED THEN   INSERT (id, name) VALUES (src.id, src.name);
MySQL/ SingleStore	ON DUPLICATE KEY UPDATE 절	INSERT INTO employees (id, name) VALUES (101, 'John Doe') ON DUPLICATE KEY UPDATE name = VALUES(name);
Postgres/ YugaByte	ON CONFLICT 절	INSERT INTO employees (id, name) VALUES (101, 'John Doe') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

 

 

주요 특징

• Oracle: UPSERT 키워드를 직접 사용하여 단일 작업으로 처리[2].
• MySQL/SingleStore: ON DUPLICATE KEY UPDATE를 통해 기존 레코드가 있으면 업데이트, 없으면 삽입[3].
• Postgres/YugaByte: ON CONFLICT ... DO UPDATE 구문으로 충돌 발생 시 업데이트 수행[5]. EXCLUDED 키워드로 삽입 시도된 값 참조.

 

 

 Upsert를 하는 기본 문법

• 각 DBMS마다 update 와 upsert 방식은 차이가 있습니다.
• Update는 테이블의 데이터 값을 변경하는 것이고,
• Upsert는 먼저 조회를 하고 데이터 중복값이 없으면 insert를 하고, 데이터가 있는 경우는 중복이 일어나므로 Update를 하는 것입니다. ( select + (insert or update ) )를 하나의 문장에서 동시에 처리 한다고 하여 "upsert"라는 용어로 표현하는 것입니다.
•  Upsert는 실제 SQL 표준 문법에 포함된 용어가 아닙니다. ( Update +  Insert  합성 )

 

• Oracle:
• UPDATE employees SET name = 'Jane Doe' WHERE id = 101; -- 단일 열 업데이트
• UPDATE employees SET department = 'HR', salary = 5000 WHERE id > 100; -- 다중 열 업데이트

 

• MySQL/SingleStore:
• UPDATE employees SET name = 'Jane Doe' WHERE id = 101; -- WHERE 조건 필수 [3]

 

• Postgres/YugaByte:
• UPDATE employees USING TTL 3600 SET name = 'Jane Doe' WHERE id = 101; -- TTL 옵션 지원 [5]

 

SingleStore에 대한 구체적인 UPSERT 구현 방식 MySQL 호환 문법(ON DUPLICATE KEY UPDATE) 또는 PostgreSQL 스타일(ON CONFLICT)을 사용이 가능합니다.

 

 

각 DB의 UPDATE 문장을 UPSERT로 변환

아래는 Oracle, MySQL, YugaByte, SingleStore에서 UPDATE 문장을 "UPSERT" 방식(존재하면 UPDATE, 없으면 INSERT)으로 바꾼 예시입니다.

---

1. Oracle

• *Oracle Update *:
• UPDATE employees SET name = 'Jane Doe' WHERE id = 101; -- 단일 열 업데이트 UPDATE employees SET department = 'HR', salary = 5000 WHERE id > 100; -- 다중 열 업데이트
• Oracle Merge into (upsert):
  Oracle에서는 MERGE 구문을 사용하여 UPSERT를 구현합니다.

MERGE INTO employees e
USING (SELECT 101 AS id, 'Jane Doe' AS name FROM dual) src
ON (e.id = src.id)
WHEN MATCHED THEN
  UPDATE SET e.name = src.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (src.id, src.name);

• 설명:
  • MERGE INTO는 일치하면 UPDATE, 없으면 INSERT를 수행합니다.
  • dual은 임시 테이블 역할.

---

2. MySQL

• MySQL Updte:
• UPDATE employees SET name = 'Jane Doe' WHERE id = 101; -- WHERE 조건 필수
• MySQL Upsert:

MySQL에서는 INSERT ... ON DUPLICATE KEY UPDATE 구문을 사용합니다.

INSERT INTO employees (id, name)
VALUES (101, 'Jane Doe')
ON DUPLICATE KEY UPDATE
  name = VALUES(name);

• 설명:
  • id가 PRIMARY KEY 또는 UNIQUE KEY일 때 동작합니다.
  • 존재하면 name을 UPDATE, 없으면 INSERT.

---

3. YugaByte (PostgreSQL 호환)

• *YugaByte update *:
• UPDATE employees USING TTL 3600 SET name = 'Jane Doe' WHERE id = 101; -- TTL 옵션 지원
• *YugaByte upsert *:

YugaByte는 PostgreSQL과 동일하게 ON CONFLICT 구문을 지원합니다.

INSERT INTO employees (id, name)
VALUES (101, 'Jane Doe')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

• 설명:
  • id가 충돌(중복)되면 UPDATE, 아니면 INSERT.
  • EXCLUDED.name은 삽입 시도한 값을 의미.

---

4. SingleStore

• MySQL Updte:

  UPDATE employees SET name = 'Jane Doe' WHERE id = 101;  -- WHERE 조건 필수 

SingleStore는 MySQL 호환이므로 ON DUPLICATE KEY UPDATE를 사용하거나, 최신 버전에서는 ON CONFLICT도 지원합니다.

MySQL 스타일

INSERT INTO employees (id, name)
VALUES (101, 'Jane Doe')
ON DUPLICATE KEY UPDATE
  name = VALUES(name);

PostgreSQL 스타일 (SingleStore 8.0 이상)

INSERT INTO employees (id, name)
VALUES (101, 'Jane Doe')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

---

정리

• Oracle: MERGE
• MySQL: INSERT ... ON DUPLICATE KEY UPDATE
• YugaByte: INSERT ... ON CONFLICT ... DO UPDATE
• SingleStore: MySQL 또는 PostgreSQL 스타일 UPSERT 모두 지원 (버전에 따라 다름)

각 DB의 UPSERT 문법은 위와 같습니다.
특정 컬럼만 UPSERT하거나, 여러 컬럼을 동시에 처리하려면 SET 부분에 원하는 컬럼을 추가하면 됩니다.

[1] https://docs.oracle.com/en/database/other-databases/nosql-database/22.3/sqlreferencefornosql/upsert-statement.html
[2] https://docs.yugabyte.com/preview/api/ycql/dml_update/
[3] https://www.bookstack.cn/read/yugabyte-2.1/e59b269d52bced00.md?wd=YugaByte+DB
[4] https://www.softwaretestinghelp.com/mysql-update-statement/
[5] https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/UPDATE.html
[6] https://www.w3schools.com/sql/sql_update.asp