ORACLE的WITH语句的一个疑惑

使用WITH语句，更新表数据，不行：

WITH VN AS (
    SELECT T.ID, T.NODE_ID, N.NODE_TYPE, N.NODE_NAME, T.NODE_LEVEL, T.RN FROM
    (        
            SELECT ID, NODE_ID, LEVEL NODE_LEVEL， ROWNUM RN
                    FROM ORG_NODE_TREE
                    START WITH PARENT_ID IS NULL
                    CONNECT BY PRIOR NODE_ID=PARENT_ID 
    ) T
    LEFT JOIN ORG_NODE N ON N.ID=T.NODE_ID
    ORDER BY T.RN
)
UPDATE ORG_NODE N
SET N.NODE_TYPE='STATION'
WHERE N.ID IN 
(
    SELECT NODE_ID FROM VN
    WHERE NODE_LEVEL=1
)

报以下错误：

[Err] ORA-00933: SQL command not properly ended

不使用WITH，可以：

WITH VN AS (
    SELECT T.ID, T.NODE_ID, N.NODE_TYPE, N.NODE_NAME, T.NODE_LEVEL, T.RN FROM
    (        
            SELECT ID, NODE_ID, LEVEL NODE_LEVEL， ROWNUM RN
                    FROM ORG_NODE_TREE
                    START WITH PARENT_ID IS NULL
                    CONNECT BY PRIOR NODE_ID=PARENT_ID 
    ) T
    LEFT JOIN ORG_NODE N ON N.ID=T.NODE_ID
    ORDER BY T.RN
)

UPDATE ORG_NODE N
SET N.NODE_TYPE='STATION'
WHERE N.ID IN 
(
    SELECT NODE_ID FROM 
(
    SELECT T.ID, T.NODE_ID, N.NODE_TYPE, N.NODE_NAME, T.NODE_LEVEL, T.RN FROM
    (        
            SELECT ID, NODE_ID, LEVEL NODE_LEVEL， ROWNUM RN
                    FROM ORG_NODE_TREE
                    START WITH PARENT_ID IS NULL
                    CONNECT BY PRIOR NODE_ID=PARENT_ID 
    ) T
    LEFT JOIN ORG_NODE N ON N.ID=T.NODE_ID
    ORDER BY T.RN
) 
WHERE NODE_LEVEL=1
) 

如果把WITH后面换成SELECT，一点问题没有，但UPDATE就是报错，好奇怪，难道WITH这种间接地写法不能用于UPDATA。

 

一种说法是：“with必须紧跟引用的select语句，而不是delete,update,merge等”

http://www.itpub.net/thread-1585644-1-1.html

提到可以用MERGE：

--wkc168 发表于 2012-3-2 13:28 
merge into c using (select * from b) a on(c.g=a.h)
when matched then
update set c.e=a.f

http://stackoverflow.com/questions/5380559/update-statement-using-with-clause

 

转载于:https://www.cnblogs.com/legendjslc/p/6876902.html