일반적인 온라인 트랜잭션 프로그램에서 UPDATE, DELETE 문장은 주로 하나의 테이블에 대해 한 건 이상의 레코드를 변경, 삭제하기위해 사용되지만, MySQL 서버에서는 여러 테이블을 조인해서 한 개 이상의 테이블의 레코드를 변경하거나 삭제하는 기능도 제공한다.

• JOIN UPDATE, JOIN DELETE로 처리하며 잘못된 데이터 보정, 일괄로 많은 레코드를 변경 및 삭제하는 경우에 유용하다.

UPDATE … ORDER BY … LIMIT n

MySQL 에서는 UPDATE, DELETE 문장에 ORDER BY 절과 LIMIT절을 동시에 사용해 특정 컬럼으로 정렬해서 상위 몇 건만 변경 및 삭제하는 것도 가능하다.

한 번에 너무 많은 레코드를 변경 및 삭제하는 작업은 MySQL 서버에 과부하를 유발하거나 다른 커넥션의 쿼리를 방해할 수도 있는데, 이 때 LIMIT절을 이용해 조금씩 잘라서 변경하거나 삭제하는 방식을 손쉽게 구현할 수 있다.

• 복제 소스 서버에서 문장을 실행하면 경고 메시지가 발생할 수 있다.
  • ORDER BY에 의해 정렬되더라도 중복된 값의 순서가 복제 소스 서버와 레플리카 서버에서 달라질 수도 있기 때문이다.
  • 프라이머리 키로 정렬하면 문제는 없지만 오류는 기록된다.
• 바이너리 로그의 포맷이 로우(ROW)일 경우 문제가 되지 않지만 문장(STATEMENT) 기반의 복제에서는 주의가 필요하다.

JOIN UPDATE

두 개 이상의 테이블을 조인해 조인된 결과 레코드를 변경 및 삭제하는 쿼리를 JOIN UPDATE라고 한다.

• 조인된 테이블 중에서 특정 테이블의 컬럼 값을 다른 테이블의 컬럼에 업데이트해야 할 때 주로 사용한다.
• 꼭 다른 테이블의 컬럼값을 참조하지 않더라도 조인되는 양쪽 테이블에 공통으로 존재하는 레코드만 찾아서 업데이트하는 용도로도 사용할 수 있다.

일반적으로 조인되는 모든 테이블에 대해 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 컬럼이 변경되는 테이블은 쓰기 잠금이 걸린다.

• 웹 서비스 같은 OLTP 환경에서는 데드락을 유발할 가능성이 높으므로 빈번하게 사용하는 것은 피하는게 좋다.
• 배치 프로그램이나 통계용 UPDPATE 문장에서는 유용하게 사용할 수 있다.

JOIN UPDATE 쿼리도 2개 이상의 테이블을 먼저 조인해야 하므로 테이블의 조인 순서에 따라 UPDATE 문장의 성능이 달라질 수 있으므로 사용하기 전에 실행 계획을 확인하는 것이 좋다.

파생 테이블을 사용하는 JOIN UPDATE

1

ALTER TABLE departments ADD emp_count INT;

테스트를 위해 departments에 해당 부서에 소속된 사원의 수를 저장하기위한 emp_count컬럼을 추가한다.

1
2
3
4
5
6
7
8
9

UPDATE departments d, dept_emp de
  SET d.emp_count=COUNT(*)
WHERE de.dept_no=d.dept_no
GROUP BY de.dept_no
;

/*
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY de.dept_no' at line 4
*/

위 쿼리는 dept_emp 테이블에서 부서별로 사원의 수를 departments 테이블의 emp_count를 업데이트 하기 위해 만든 쿼리다.

GROUP BY를 이용해서 해당 부서의 사원을 업데이트 할 수 있을 것 처럼 보이지만 JOIN UPDATE 문장에서는 GROUP BY, ORDER BY절을 사용할 수 없기 때문에 위와 같은 에러가 발생한다.

이렇게 문법적으로 지원하지 않는 쿼리는 서브쿼리를 이용한 파생 테이블을 사용하여 의도한 대로 처리할 수 있다.

1
2
3
4
5
6
7
8

UPDATE departments d, (
  SELECT de.dept_no, COUNT(*) AS emp_count
  FROM dept_emp de
  GROUP BY de.dept_no
  ) dc
  SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no
;

서브쿼리로 dept_dmp 테이블을 dept_no로 그루핑하고, 그 결과를 파생 테이블로 저장한 후, departments 테이블을 조인하여 업데이트 처리하게 된다.

여러 레코드 UPDATE

하나의 UPDATE 문장으로 여러 개의 레코드를 업데이트하는 경우 아래와 같이 모든 레코드 값을 동일한 값으로만 업데이트 할 수 있었다.

1
2

UPDATE departments SET emp_count = 10;
UPDATE departments SET emp_count = emp_count + 10;

하지만 MySQL 8.0 버전부터는 레코드 생성(Row Constructor) 문법을 이용해 레코드별로 서로 다른 값을 업데이트 할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE user_level (
  user_id BIGINT NOT NULL,
  user_lv INT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (user_id)
);

UPDATE user_level ul
  INNER JOIN (VALUES ROW(1, 1), ROW(2, 4)) new_user_level (user_id, user_lv)
    ON new_user_level.user_id = ul.user_id
  SET ul.user_lv = ul.user_lv + new_user_level.user_lv
;

VALUES ROW(...), ROW(...), ... 문법을 사용하면 SQL 문장 내에서 임시 테이블을 생성하는 효과를 낼 수 있다.

위 예제는 2건의 레코드를 가지는 임시 테이블을 생성하고, 임시 테이블과 조인하여 업데이트를 수행하는 JOIN UPDATE 효과를 낼 수 있다.

JOIN DELETE

JOIN DELETE 문장을 사용하려면 단일 테이블 처리시와 조금 다른 문법으로 쿼리를 작성해야한다.

한개 테이블 데이터 삭제

1
2
3
4
5
6

DELETE e
FROM employees e
  JOIN dept_emp de ON de.emp_no = e.emp_no
  JOIN departments d ON d.dept_no = de.dept_no
WHERE d.dept_no = 'd001'
;

위 예제는 3개의 테이블을 조인한 후 조인이 성공한 레코드에 대해 employees 테이블의 레코드만 삭제한다.

• 일반적으로 하나의 테이블에서 레코드를 삭제할 때는 DELETE FROM table ... 같은 문법으로 사용하지만 JOIN DELETE 문장에서는 DELETE와 FROM절 사이에 삭제할 테이블을 명시해야 한다.

여러개 테이블 데이터 삭제

1
2
3
4
5
6

DELETE e, de, d
FROM employees e
  JOIN dept_emp de ON de.emp_no = e.emp_no
  JOIN departments d ON d.dept_no = de.dept_no
WHERE d.dept_no = 'd001'
;

DELETE와 FROM 사이에 표시된 테이블에 대해 삭제 처리된다.