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.

Answer 2

You can update using clause where id in (select ... connect by...) or you can use merge:

merge into htable h
using (select distinct id 
         from htable 
         where status <> 'CLOSE'
         connect by prior parent_id = id 
         start with parent_id = 11) src
on (h.id = src.id)
when matched then update set status = 'INACTIVE'

Test data:

create table htable (id number(4), parent_id number(4), status varchar2(10));
insert into htable values (   1, null, 'ACTIVE');
insert into htable values (  11,    1, 'CLOSE');
insert into htable values (  12,    1, 'ACTIVE');
insert into htable values ( 111,   11, 'ACTIVE');
insert into htable values ( 112,   11, 'ACTIVE');
insert into htable values ( 121,   12, 'ACTIVE');
insert into htable values (   2, null, 'ACTIVE');
insert into htable values (  21,    2, 'ACTIVE');
insert into htable values ( 211,   21, 'ACTIVE');
insert into htable values ( 212,   21, 'ACTIVE');

and after merge:

   ID PARENT_ID STATUS
----- --------- ----------
    1           INACTIVE
   11         1 INACTIVE
   12         1 ACTIVE
  111        11 INACTIVE
  112        11 INACTIVE
  121        12 ACTIVE
    2           ACTIVE
   21         2 ACTIVE
  211        21 ACTIVE
  212        21 ACTIVE