PostgreSQL和Oracle:从通用子查询更新多个表 [英] Postgresql and Oracle: update multiple tables from common subquery

问题描述

你好，我有4张桌子

第一个表格是菜单 有专栏:

first table is menu have got column:

1. 编号PK
2. parent_id号FK到menu.Id
3. 标题字符变化(250)
4. softdel布尔默认值为false

第二张表是页面:

1. id为PK
2. menu_id作为FK到menu.id
3. page_id作为对page.id的FK
4. softdel布尔默认值为false

第三张表是文章:

1. id作为PK和FK到page.id
2. softdel布尔默认设置为false

和第四个表格 article_lang :

1. partial_id为PK
2. id为article.id的FK
3. 语言字符
4. softdel布尔默认设置为false

并且当我删除"(我的意思是将softdel设置为true)具有ID例如200的菜单时，我还需要创建更新语句，同时还要对parent_id = 200的所有菜单以及menu_id = menus_id和page_id = pages.id等等的文章...

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

我只需要1条更新语句就可以做到?

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

如果我可以创建JPA查询或EJB查询，那就太好了

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

在oracle中我写语句:

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 :/

推荐答案

类似的东西

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

这篇关于PostgreSQL和Oracle:从通用子查询更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！