[Database] 소소한 에러 해결 - MySQL 워크벤치 SAFE MODE, MySQL You can't specify target table 에러

지난번에 적으려다가 시간 없어서 스킵했던 내용들 주섬주섬 적어보기

⛑️ 1. Safe mode 해제

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec

이건 mysql 워크벤치에서 걸어둔 안전모드 때문에 발생하는 에러다. 사용자가 실수로 데이터를 바꾸거나 날려버리지 않게 하려고 걸어둔 것인데 이로 인해서 키 값을 이용한 update나 delete 구문이 실행되지 않을 수 있다.

워크벤치 ui 창에서 옵션을 바꿔줄 수도 있지만 ...
개발자 간지는 역시 명령어 입력임.

SET SQL_SAFE_UPDATES = 0;

쿼리 쓰는 것이 간지나므로 난 쿼리로 해결한다.
물론 이러고 또 까먹어서 다시 검색함 ㅠ 언제쯤 외울래 ㅠ
다시 설정하고 싶다면 1로 바꿔주면 된다.

🚨 2. You can't specify target table 에러

이것은 MySQL 특성상 발생하는 에러이다.

DELETE FROM Products
WHERE PRODUCT_ID NOT IN (
	SELECT MIN(PRODUCT_ID)
    FROM Products
    GROUP BY PRODUCT_NAME
);

이 쿼리의 의도는 PRODUCT_NAME을 기준으로 데이터를 그룹핑한 후, 각 그룹의 레코드 중에서 PRODUCT_ID가 가장 작은 행을 제외하고 삭제하는 것이다. 즉 중복된 행 중 하나만 남기고 삭제하고자 하는 것.

하지만 위의 쿼리는 오라클에서만 가능하고 MySQL에서는 에러를 뱉어낸다. MariaDB도 됨. MySQL만 안됨 ㅡㅡ

이유는 MySQL에서는 데이터를 추가하거나 갱신할 경우 동일한 테이블로 서브쿼리를 사용할 수 없다는 규칙이 있기 때문이다.

🥄 해결 1. 임시테이블 생성

CREATE TEMPORARY TABLE tmp AS
SELECT MIN(PRODUCT_ID) AS MIN_ID 
FROM PRODUCT
GROUP BY PRODUCT_NAME;

CREATE TEMPORARY TABLE 명령어를 통해서 임시 테이블을 생성한 뒤 PRODUCT_NAME에 대한 최소 PRODUCT_ID 값을 저장한다.

DELETE FROM PRODUCT3
WHERE PRODUCT_ID NOT IN (SELECT MIN_ID FROM tmp);
-- 임시테이블 삭제 
DROP TEMPORARY TABLE IF EXISTS tmp;

임시 테이블에 없는 PRODUCT_ID를 가진 행을 삭제한다. 그리고 임시테이블을 삭제해준다.

🥄 해결 2. 서브쿼리 안에 서브쿼리로 한 번 더 감싸주기

동일 테이블로 서브쿼리를 사용할 수 없다면, 인라인 뷰(FROM절 서브쿼리, 하나의 테이블처럼 사용 가능)로 한 번 더 감싸버리면 그만이야 ^0^

몇 번의 시행착오를 겪었다.

* 컬럼 별칭(Alias) 주의

# ALIAS를 안 썼을 때의 컬럼명 확인 
SELECT MIN(PRODUCT_ID)
FROM Products3
GROUP BY PRODUCT_NAME;

# ALIAS를 썼을 때의 컬럼명 확인
SELECT MIN(T.PRODUCT_ID) AS PRODUCT_ID 
FROM Products3 AS T 
GROUP BY T.PRODUCT_NAME;

이 둘은 같은 쿼리지만 아주 큰 간극을 가지고 있다... 실행을 한 결과가 완전 다르다.

이름(컬럼명)이 아주 다르다 ㅠㅠㅠㅠ
따로 alias를 주지 않게 되면 아주 길고 복잡한 이름이 되고, 귀찮아진다.

서브쿼리를 사용해야 할 때는 이렇게 하나씩 차근차근 작은 쿼리부터 큰 쿼리로 살을 붙여나가는 것이 좋은 것 같다. 안그러면 꼭 실수함.

* 모든 서브쿼리는 별칭이 있어야 한다

또, 하나의 실수 ...

DELETE FROM Products3
WHERE PRODUCT_ID NOT IN (
	SELECT PRODUCT_ID FROM (SELECT MIN(T.PRODUCT_ID) AS PRODUCT_ID FROM Products3 AS T GROUP BY T.PRODUCT_NAME) 
);

이렇게 하면 완벽할 줄 알았으나?

Error Code: 1248. Every derived table must have its own alias	0.000 sec

모든 서브쿼리에는 alias가 있어야 한다!! 그래서 괄호 전체를 alias로 별칭을 주어야만 한다. (* 그런데 이것도 오라클에선 상관 없다고 함 MySQL 넘 예민한듯 ;;)

해결 쿼리문

DELETE FROM PRODUCT 
WHERE PRODUCT_ID NOT IN (
	SELECT PRODUCT_ID 
    FROM (SELECT MIN(T.PRODUCT_ID) AS PRODUCT_ID 
    	FROM PRODUCT AS T GROUP BY T.PRODUCT_NAME) AS P
);

이렇게 하면 임시 테이블을 따로 생성할 필요 없이, FROM 절에 서브쿼리를 사용해서 하나의 테이블처럼 사용해서 문제를 해결할 수 있다.

---

🙄 느낀점

서브쿼리는 좋은 거구나...! 그렇지만 정말 봐도봐도 모르는 게 뿅 튀어나오고... 하여튼 신기함

그리고 내가 이걸 좀 잘 기억해놨다가 간지나게 써먹었음 좋겠다. 매번 '아 이거 그건데...!' 하면서 구굴 검색 중 ㅋㅋㅋㅋ ㅠㅠㅠㅠ 능지야 힘을 좀 내봐 ...

---

본 포스팅은 글로벌소프트웨어캠퍼스와 교보DTS가 함께 진행하는 챌린지입니다