[MySQL] Join을 이용한 Update, Delete

Join을 이용한 Update, Delete

---

간혹 join을 이용하여 데이터를 update  할 경우가 발생한다.

의외로 Join을 이용하여 조건에 맞는 데이터를 변경하는 방법은 매우 유용하다.

아래는 emp, emp_dept, dept 3개로 구성된 간단한 샘플이다.

CREATE TABLE emp(
  id  INT NOT NULL AUTO_INCREMENT,
  emp_name VARCHAR(10) ,
  salary DECIMAL(10,2),
  hire_date DATETIME ,
  fire_date  DATETIME ,
  PRIMARY KEY (id)
);
INSERT INTO emp(emp_name, salary, hire_date, fire_date) 
VALUES('철수', 1000000, '2017-01-01', NULL),('영희', 2000000, '2016-01-01', NULL),
           ('길동', 3000000, '2015-01-01', NULL),('바부', 6000000, '2014-01-01', NULL);
 
CREATE TABLE emp_dept(
  emp_id  INT NOT NULL,
  dept_id  INT NOT NULL,
  reg_date DATETIME ,
  PRIMARY KEY (emp_id, dept_id)
);

INSERT INTO emp_dept(emp_id, dept_id, reg_date) 
VALUES(1, 1, '2017-01-01'),(2, 1, '2016-06-01'),(3, 2, '2017-01-01'),(4, 3, '2016-06-01');


CREATE TABLE dept(
  id  INT NOT NULL AUTO_INCREMENT,
  dept_name VARCHAR(10) ,
  location VARCHAR(10) ,
  PRIMARY KEY (id)
);
INSERT INTO dept(dept_name, location) 
VALUES('기획실', '서울'),('홍보실', '부산'),('영업부', '도쿄'),('해외실사팀', '홍콩');
--------------------------------------------------------------------------------------

SELECT a.id AS dept_id,
       a.dept_name,
       a.location,
       c.id AS emp_id,
       c.emp_name,
       c.salary,
       c.hire_date,
       b.reg_date       
  FROM dept a,
       emp_dept b,
       emp c
 WHERE a.id = b.dept_id
   AND b.emp_id = c.id;

dept_id  dept_name  location      emp_id  emp_name  salary      hire_date            reg_date             
-------  ---------  --------      ------  --------  ----------  -------------------  ---------------------
      1  기획실        서울             1  철수        1000000.00  2017-01-01 00:00:00  2017-01-01 00:00:00  
      1  기획실        서울             2  영희        2000000.00  2016-01-01 00:00:00  2016-06-01 00:00:00  
      2  홍보실        부산             3  길동        3000000.00  2015-01-01 00:00:00  2017-01-01 00:00:00  
      3  영업부        도쿄             4  바부        6000000.00  2014-01-01 00:00:00  2016-06-01 00:00:00  

전체적으로 조회 하면 각 부서의 위치와 해당 부서의 인력 및 인력의 급여와 입사일로 그성되어 있는 테이블3개의 구조다.

Q1. 최근 서울의 교통비가 올라서 서울 근무자들에게는 교통비 명목으로 급여에 50,000을 인상해주기로 했다.

급여가 있는 emp 테이블로는 서울 근무자인지 알수가 없다. 이런 경우 많은 개발자 들이 dept 테이블에서 부서가 서울인 부서를 찾고, 이를 이용해 루프를 돌면서 처리 한다.

아래와 같이 조인을 이용해 update가능하다.

UPDATE emp a, emp_dept b, dept c
  SET  a.salary = a.salary + 50000
 WHERE a.id = b.emp_id
   AND b.dept_id = c.id
   AND c.location = "서울";

-----------------------------------------------------

SELECT* FROM emp;
    id  emp_name  salary      hire_date            fire_date  
------  --------  ----------  -------------------  -----------
     1  철수        1050000.00  2017-01-01 00:00:00  (NULL)     
     2  영희        2050000.00  2016-01-01 00:00:00  (NULL)     
     3  길동        3000000.00  2015-01-01 00:00:00  (NULL)     
     4  바부        6000000.00  2014-01-01 00:00:00  (NULL)   

Q1. 부서 원이 없는  부서를 삭제 하라.

dept 테이블과 emp_dept 테이블의 Lelf join을 이용하여 emp_id가 없는 dept 테이블의 row를 찾아 삭제 한다.

Left Join 은 MySQL Join정리(http://cirius.tistory.com/1135) 를 참조 하세요.

DELETE a FROM dept a LEFT JOIN emp_dept b ON a.id = b.dept_id
 WHERE b.dept_id IS NULL;

---------------------------------------------------------

SELECT* FROM dept;
    id  dept_name  location  
------  ---------  ----------
     1  기획실        서울    
     2  홍보실        부산    
     3  영업부        도쿄   

자세히 보면 모두 select를 이용하여 Update or Delete 하고자 하는 row를 찾을 수 있다면, 매우 쉽게 처리 가능하다.

---