title: How to delete a tree node and its childs records from a table (without cascade delete)?
body: <p>I have this table in postgreSQL v9.1:</p>
  
  <pre><code>CREATE TABLE ad_treenodemm
  (
    ad_tree_id numeric(10,0) NOT NULL,
    node_id numeric(10,0) NOT NULL,
    ad_client_id numeric(10,0) NOT NULL,
    ad_org_id numeric(10,0) NOT NULL,
    name character varying(60) NOT NULL,
    isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
    created timestamp without time zone NOT NULL DEFAULT now(),
    createdby numeric(10,0) NOT NULL,
    updated timestamp without time zone NOT NULL DEFAULT now(),
    updatedby numeric(10,0) NOT NULL,
    parent_id numeric(10,0),
    seqno numeric(10,0),
    CONSTRAINT ad_treenodemm_pkey PRIMARY KEY (ad_tree_id , node_id ),
    CONSTRAINT adtree_adtreenodemm FOREIGN KEY (ad_tree_id)
        REFERENCES adempiere.ad_tree (ad_tree_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT ad_treenodemm_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
  )
  </code></pre>
  
  <p><br/>
  Important columns description:<br>
  * ad_tree_id = The tree group id (connected to ad_tree table)<br>
  * node_id    = The node id<br>
  * parent_id  = The parent node id (if 0 => means the node is on the top)<br></p>
  
  <p>The rests of columns can be ignored.</p>
  
  <p><br>
  For example, I have the ad_treenodemm table data presentation like this:</p>
  
  <pre><code># Group1 (all node belows are assigned with ad_tree_id=1001)
      -Accounting (node_id=101, parent_id=0)
          -Costing (node_id=202, parent_id=101)
              -Cost Type (node_id=103, parent_id=202)
              -Cost Element (node_id=24, parent_id=202)
          -Client Accounting Processor (node_id=105, parent_id=101)
          -Reset Accounting (node_id=6, parent_id=101)
              ...
  
      -Finance (node_id=4110, parent_id=0)
          ...
  
  # Group2 (all node belows are assigned with ad_tree_id=1002)
      ...
  </code></pre>
  
  <p>Let's say, I want to delete <strong>Accounting</strong> node and its child nodes in the Group1. That means, it also deletes node: Costing, Cost Type, Cost Element, Reset Accounting,...etc. How to do it?</p>
  
  <p>The solution can be in SQL or Java language with JDBC (but SQL would be preferred if possible).</p>
  
  <p><br>
  <strong>UPDATE:</strong>
  I found a solution with WITH RECURSIVE (CTE) sql, however it's not too elegant:</p>
  
  <pre><code>WITH RECURSIVE temp(ad_tree_id, node_id, parent_id) AS (
      SELECT a.ad_tree_id, a.node_id, a.parent_id
      FROM ad_treenodemm a 
      WHERE ad_tree_id=1001 AND node_id=101      -- look at this
  
      UNION ALL
  
      SELECT b.ad_tree_id, b.node_id, b.parent_id
      FROM ad_treenodemm b
      INNER JOIN temp c on c.node_id = b.parent_id
      WHERE b.ad_tree_id=c.ad_tree_id
  )
  DELETE FROM ad_treenodemm a
  WHERE (a.ad_tree_id, a.node_id) IN (
      SELECT ad_tree_id, node_id FROM temp
  );
  </code></pre>
  
  <p>You see that I put the argument ( WHERE ad_tree_id=1001 AND node_id=101) inside the WITH clause. Anyone know how to improve the SQL by putting the argument statement outside the WITH clause?</p>
  
  <p>For anyone who want to experiment the query without deleting the records, use this:</p>
  
  <pre><code>WITH RECURSIVE temp(ad_tree_id, node_id, parent_id) AS (
      SELECT a.ad_tree_id, a.node_id, a.parent_id
      FROM ad_treenodemm a 
      WHERE ad_tree_id=1001 AND node_id=101
  
      UNION ALL
  
      SELECT b.ad_tree_id, b.node_id, b.parent_id
      FROM ad_treenodemm b
      INNER JOIN temp c on c.node_id = b.parent_id
      WHERE b.ad_tree_id=c.ad_tree_id
  )
  SELECT * FROM ad_treenodemm a
  WHERE (a.ad_tree_id, a.node_id) IN (
      SELECT ad_tree_id, node_id FROM temp
  )
  ORDER BY a.parent_id, a.node_id
  </code></pre>