title: Postgresql and Oracle: update multiple tables from common subquery
body: <p>Hello i have got 4 tables</p>
  
  <p>first table is <strong>menu</strong>
  have got column: </p>
  
  <ol>
  <li>Id number PK</li>
  <li>parent_id number FK to menu.Id</li>
  <li>title character varying(250)</li>
  <li>softdel boolean default false</li>
  </ol>
  
  <p>second table is <strong>page</strong>:</p>
  
  <ol>
  <li>id as PK</li>
  <li>menu_id as FK to menu.id</li>
  <li>page_id as FK to page.id</li>
  <li>softdel boolean default false</li>
  </ol>
  
  <p>third table is <strong>article</strong>:</p>
  
  <ol>
  <li>id as PK and FK to page.id</li>
  <li>softdel boolean default set to false</li>
  </ol>
  
  <p>and fourth table <strong>article_lang</strong>:</p>
  
  <ol>
  <li>partial_id as PK</li>
  <li>id as FK to article.id</li>
  <li>language character</li>
  <li>softdel boolean default set to false</li>
  </ol>
  
  <p>and i need to create update statement when i 'delete' (I mean set softdel true) menu with id for example 200 i also set softdel = false to  all menus which parent_id = 200 and also all pages which menu_id = menus_id and articles which page_id = pages.id and so on....</p>
  
  <p>i need only 1 update statement its possible to do it ??</p>
  
  <p>it would be wonderful if i can create JPA query or EJB query :)</p>
  
  <p>in oracle i write statement : </p>
  
  <pre><code>update pub_menu pm set softdel = 0 where pm.id in (
  with menu_tree(id, parent_id) as (
    select 
      t1.id , t1.parent_id
      from menu t1
      where t1.id = 454
  
      union all
      select 
      t2.id , t2.parent_id
        from menu_tree
        join menu t2 on menu_tree.id = t2.parent_id
  )
  select id from menu_tree
  
  )
  
  update menu_page pmp set softdel = 1 where pmp.menu_id in (
  with menu_tree(id, parent_id) as (
    select 
      t1.id , t1.parent_id
      from menu t1
      where t1.id = 454
  
      union all
      select 
      t2.id , t2.parent_id
        from menu_tree
        join menu t2 on menu_tree.id = t2.parent_id
  )
  select id from menu_tree
  
  )
  </code></pre>
  
  <p>its working but i thing that is incorrect to do it like that :/</p>