[MySQL] UPDATE 쿼리 WHERE 절 서브쿼리 문제

문제점

UPDATE tbl SET score = score - 1 WHERE score > (SELECT score FROM tbl WHERE id = 1);

실행해 보면...

ERROR 1093 (HY000): You can't specify target table 'tbl' for update in FROM clause

 

UPDATE 대상 테이블과 WHERE 절에 사용된 sub query에는 동일한 테이블명이 있으면 안된다.

꼼수로 가능하게 하는 법

UPDATE tbl SET score = score -1
WHERE score > (SELECT t.score FROM (SELECT score FROM tbl WHERE id = 1) AS t);

다른 예제

UPDATE gd_order_log_each SET flag=5 WHERE ordno IN (
	SELECT a.ordno FROM gd_order AS a
		JOIN gd_order_log_each AS b ON a.ordno=b.ordno
	WHERE a.step=4 AND b.flag!=5
    	GROUP BY b.ordno)

또는

UPDATE gd_order_log_each SET flag=5 WHERE ordno IN (
	SELECT tbl.ordno FROM (SELECT a.ordno FROM gd_order AS a
		JOIN gd_order_log_each AS b ON a.ordno=b.ordno
	WHERE a.step=4 AND b.flag!=5
	GROUP BY b.ordno) AS tbl)