SQL syntax for update query with connect by prior

Question

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;

I got SQL Error: ORA-00933: SQL command not properly ended. How can I update table hierarchically?

Edit

I also tried to put the where condition in the start with, but it does not help:

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

Answer 1

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

So what you can do is this:

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);

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.