UPDATE/DELETE

1. UPDATE/DELETE 기본 폼

-- UPDATE [테이블] SET [열] = '변경할값' WHERE [조건]

UPDATE user SET Money = 10000 , item1 = '티셔츠';

-- DELETE FROM [테이블] WHERE [조건]

DELETE FROM user WHERE item1='티셔츠';

2. UPDATE/DELETE ORDER BY ... LIMIT N

UPDATE와 DELETE는 WHERE 조건절에 일치하는 모든 레코드를 업데이트 하는 것이 일반적인 처리 방식이지만, ORDER BY와 LIMIT절을 동시에 사용해 특정 칼럼으로 정렬해서 상위 몇건만 처리할 수 있다.

DELETE FROM employees ORDER BY last_name LIMIT 10;

3 JOIN UPDATE

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

UPDATE test t, employees e
	SET t.first_name=e.first_name
    WHERE e.emp_no=t.emp_no;

위의 예제는 test 테이블과 employees 테이블을 사원번호로 조인한 후, employees의 first_name 칼럼의 값을 test 테이블의 first_name 칼럼으로 복사한다. 이 때 JOIN의 순서에 따라 UPDATE 문장의 성능이 달라질 수 있다.

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

다음은 dept_emp 테이블에서 부서별로 사원의 수를 departments 테이블의 emp_count 칼럼에 업데이트하기 위해 만든 쿼리다. 하지만 해당 쿼리는 에러를 발생시킨다. JOIN UPDATE 문장에서는 GROUP BY 나 ORDER BY 절을 사용할 수 없기 때문이다. 이러한 작업을 처리하기 위해서는 서브쿼리를 이용한 파생 테이블을 사용해야한다.

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
    
 --STRAIGHT_JOIN 사용시
 UPDATE departments d,
	(SELECT de.dept_no, COUNT(*) AS emp_count
    FROM dept_emp de
    GROUP BY de.dept_no) dc
	STRAIGHT_JOIN departments d ON dc.dept_no=d.dept_no
    SET d.emp_count=dc.emp_count

이 예제 쿼리와 같이 일반테이블이 조인될 때는 임시 테이블이 드라이빙 테이블이 되는 것이 일반적으로 더 빠른 성능을 보여준다. 보통 MYSQL의 옵티마이저거 알아서 최적의 조인방향을 선택하지만, 원하는 조인의 방향이 있을 때는 STRAIGHT JON을 사용한다. 왼쪽에 명시된 테이블이 드라이빙 테이블이 되며 오른쪽 테이블은 드리븐 테이블이된다.

 --INNER_JOIN 사용시
 UPDATE departments d,
	(SELECT de.dept_no, COUNT(*) AS emp_count
    FROM dept_emp de
    GROUP BY de.dept_no) dc
	INNER departments d ON dc.dept_no=d.dept_no
    SET d.emp_count=dc.emp_count

STRAIGHT_JOIN 키워드에 INNER JOIN, LEFT JION과 같은 조인 키워드로 사용되기도 한다.

4. JOIN DELETE

 DELETE e
 FROM employees e, dept_emp de, deparments d
 WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';

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

 DELETE e, de
 FROM employees e, dept_emp de, deparments d
 WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';
 
 DELETE e, de, d
 FROM employees e, dept_emp de, deparments d
 WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';

다음과 같이 한 테이블 만이 아니라 여러 테이블에서 레코드를 삭제할 수 있다.

 
 DELETE e, de, d
 FROM deparments d
 	STRAIGHT_JOIN dept_emp de ON de.dept_no=d.dept_no
    STRAIGHT_JOIN employees e ON de.emp_no=de.emp_no
 WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';
 
  DELETE /* JOIN_ORDER(d,de,e) */ e, de, d
 FROM deparments d
 	INNER JOIN dept_emp de ON de.dept_no=d.dept_no
    INNER JOIN employees e ON de.emp_no=de.emp_no
 WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';

JOIN UPDATE와 마찬가지로 STRAIGHT_JOIN이나 JOIN_ORDER를 통해 조인 순서를 정할 수 있다.