之前连接的更新查询的sql语法 [英] sql syntax for update query with connect by prior

问题描述

我开始使用sql，并且遇到了层次结构查询.同时，我成功地使用connect by prior命令选择了行，但是未能更新.这是我的更新查询:

I start to work with sql and I faced up with hierarchical queries. meanwhile I success to select the rows with connect by prior command but failed to update. Here is my update query:

update HTABLE set status = 'INACTIVE'
  WHERE STATUS <> 'CLOSE'
  Connect by prior PARENT_ID=ID start with PARENT_ID=12345;

我得到了SQL Error: ORA-00933: SQL command not properly ended，并且很高兴知道如何使用层次结构来更新表.

I got SQL Error: ORA-00933: SQL command not properly ended and will be glad to know how to update table using hierarchically..

谢谢！

编辑 我还尝试将where条件放在start with中，无济于事:

EDIT I also tried to put the where condition in the start with, not helps:

update HTABLE set status = 'INACTIVE'
  Connect by prior PARENT_ID=ID start with PARENT_ID=12345 AND STATUS <> 'CLOSE';

推荐答案

分层查询仅适用于SELECT.它不适用于UPDATE(我同意，如果这样做的话，可能会很整洁).

The hierarchical query only works with SELECT. It doesn't work with UPDATE (I agree it could be neat if it did).

所以你可以做的是这个

update HTABLE 
set status = 'INACTIVE'
WHERE STATUS <> 'CLOSE'
and id in ( select c.id
            from htable
            connect by prior ID = PARENT_ID 
            start with PARENT_ID = 12345);

请注意列connect by prior ID = PARENT_ID的顺序.通常，我们想从START WITH行开始沿着树走，这就是我所做的.您的订购connect by prior PARENT_ID = ID将树从12345爬到它的父母，祖父母等处.如果这是您想要的，请切换回connect by子句.

Note the order of columns connect by prior ID = PARENT_ID. Normally we want to walk down the tree from the START WITH row, which is what I've done. Your ordering connect by prior PARENT_ID = ID walks up the tree from 12345 to its parents, grandparents, etc. If that's what you want, switch the connect by clause back.

这篇关于之前连接的更新查询的sql语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！