Postgresql and Oracle: update multiple tables from common subquery

Question

Hello i have got 4 tables

first table is menu have got column:

1. Id number PK
2. parent_id number FK to menu.Id
3. title character varying(250)
4. softdel boolean default false

second table is page:

1. id as PK
2. menu_id as FK to menu.id
3. page_id as FK to page.id
4. softdel boolean default false

third table is article:

1. id as PK and FK to page.id
2. softdel boolean default set to false

and fourth table article_lang:

1. partial_id as PK
2. id as FK to article.id
3. language character
4. softdel boolean default set to false

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....

i need only 1 update statement its possible to do it ??

it would be wonderful if i can create JPA query or EJB query :)

in oracle i write statement :

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

)

its working but i thing that is incorrect to do it like that :/

Youngyoungblood

You could probably chain the four updates, by adding a RETURNING some_id to the first three updates, and using that value in the next update. Without a ddl, I won't elaborate. – Accepted

It may be easyer to propagate softdel with triggers. – Bloodred

Yes its working in postgresql but i need sql that running on both postgresql and oracle databases – Signora

I think that I wouldn't even do the update -- checking whether the parent rows have softdel set to true would probably be effective and efficient – Mountfort

Answer 1

Something like:

with recursive menu_tree (id, parent_id) as (
   select id, parent_id
   from menu
   where id = 200
   union all 
   select c.id, c.parent_id
   from menu c
     join menu_tree p on p.id = c.parent_id
)
, deleted_menus (menu_id) as (
   update menu
      set softdel = true
   where id in (select id from menu_tree)
   returning menu.id
),
deleted_pages (page_id) as (
   update page
      set softdel = true
   where menu_id in (select menu_id from deleted_menus)
   returning page.id
),
deleted_articles (article_id) as (
   update article
     set softdel = true
   where page_id in (select page_id from deleted_pages)
)
update article_lang
   set softdel = true
where id in (select article_id from deleted_articles);

Responsion

IMHO the WHERE ... IN(...) could be replaced by a EXISTS, or just by an ordinary UPDATE target_table SET xx=true FROM src_table WHERE same_condition – Accepted

@wildplasser: probably, but I don't think it will make a difference (and I'm more used to writing standard SQL UPDATEs :) rather than using a join) – Responsion

No, it won't make a difference (I suppose the IN(...) fields are not nullable), but I actually hate IN(...) clauses, and prefer correlated subqueries ... – Accepted

Yes its working well in postgresql but i need also running this update query on oracle database :/ – Signora

@ŁukaszWoźniczka: this is not possible in Oracle. You will need to run several updates to achieve this (or use triggers) – Responsion

@a_horse_with_no_name but your first statement: update menu set softdel = true where id = 200 or parent_id = 200 returning menu.id, menu.parent_id update only first menu not all submenus ..... – Signora

yes you have got right but this update must be recursively can you update this statement please – Signora

yeea you are the best thanks a lot :) now i must write something similarly for database oracle :D – Signora

Answer 2

It would be easier if you just deleted them and let the foreign-keys cascade.

However, you can write a short trigger function that does the extra deletes for you. You'll want one for each of the first 3 tables, but they're all basically the same. They need to update the next table in the chain based on OLD.id in an AFTER trigger for each ROW.

Examples of trigger functions in the manuals.

Dilapidation

Answer 3

You can combine multiple updates with CTE:

WITH u1 AS (UPDATE menu SET softdel = TRUE WHERE menu.id = 200),
     u2 AS (UPDATE menu SET softdel = FALSE WHERE menu.parent_id = 200),
     u3 AS (...),
     u4 AS (...),
     -- and so on
SELECT 1

Confectionary

This is not correct syntax at least for Oracle: ORA-00928: missing SELECT keyword – Aboulia

Answer 4

Oracle doesn't allow any single sql statement to update multiple tables. So regular CTE couldn't help to avoid code duplication in this case.

But this can be worked around by using PL/SQL FOR LOOP IMPLICIT CURSOR like this:

begin
  for cur 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
  ) loop
    update pub_menu pm set softdel = 0 where pm.id = cur.id;
    update menu_page pmp set softdel = 1 where pmp.menu_id = cur.id;
  end loop cur;
end;

Aboulia