Question

mysql 如何对一张表进行嵌套查询

例如
UPDATE RES_OBJECT SET MULTISELECT_ID=(SELECT MULTISELECT_ID FROM RES_OBJECT WHERE RES_ID=PARENT_NODE_RES_ID)
WHERE RES_OBJECT.MULTISELECT_ID IS NULL OR RES_OBJECT.MULTISELECT_ID=0;

Answer 1

不可以哦，这不是MYSQL的问题，数据库都有这个规定：不允许UPDATE的子查询里面有被UPDATE的那个表。

我一般采取两种方法，一是利用SQL文本：
SELECT CONCAT("UPDATE RES_OBJECT SET MULTISELECT_ID=",
B.MULTISELECT_ID,"WHERE RES_ID=",A.RES_ID,";")
INTO OUTFILE ABC.SQL
FROM RES_OBJECT A,RES_OBJECT B
WHERE B.RES_ID=A.PARENT_NODE_RES_ID)
AND (RES_OBJECT.MULTISELECT_ID IS NULL OR RES_OBJECT.MULTISELECT_ID=0);

SOURCE ABC.SQL;

另外一种方法是利用临时表：
SELECT B.MULTISELECT_ID,A.RES_ID
FROM RES_OBJECT A,RES_OBJECT B
WHERE B.RES_ID=A.PARENT_NODE_RES_ID)
AND (RES_OBJECT.MULTISELECT_ID IS NULL OR RES_OBJECT.MULTISELECT_ID=0)
INTO TEMP TABLE TMP;

UPDATE RES_OBJECT SET MULTISELECT_ID=
(SELECT MULTISELECT_ID FROM TMP WHERE TMP.RES_ID=RES_OBJECT.RES_ID)
WHERE EXISTS
(SELECT MULTISELECT_ID FROM TMP WHERE TMP.RES_ID=RES_OBJECT.RES_ID);

Answer 2

你这是更新，不是查询

update a
set a.MULTISELECT_ID=b.MULTISELECT_ID
from RES_OBJECT a,RES_OBJECT b
where (a.MULTISELECT_ID is null or
a.MULTISELECT_ID=0)
and b.RES_ID=PARENT_NODE_RES_ID