在多个表的同一SQL中进行UPDATE和DELETE?

本文介绍了在多个表的同一SQL中进行UPDATE和DELETE?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用Oracle DB，是否可以更新一个表并删除另一个表(匹配的行)?

With Oracle DB, is it possible to update one table and delete (matching rows) in other?

我尝试了各种形式的MERGE，但我都得到了:

I tried various forms of MERGE but I get either:

(可能是由视图中的UNION ALL引起的)

(probably caused by UNION ALL in the view)

或

此(联接视图")建议以任何形式指定两个表对于MERGE是不可行的.正确吗?

This ("join view") suggests that specifying two tables in any form is a no-go for MERGE. Is that correct?

(使用单个语句的原因是:性能，一致性和清晰度)

(the reasons for using a single statement are: performance, consistency and clarity)

我的Oracle数据库的版本是11.2.

My Oracle DB is of version 11.2.

实际问题是这样的:

我们(一个图书馆)有一个 books 的(父)表和一个 content 的(子)表(每本书一本或零本，它有FK books 表).每年，我们都会为每本10年以上的图书(如果情况与这里的问题无关，请简化条件)来设置工作，我们在 books中设置一个名为RETIRED的列，其值为"YES" 表并删除 content 表中的行(如果存在).

We (a library) have a (parent) table of books and a (child) table of content (one or zero per book, it has a FK to the books table). Every year we run a job that for each book that is older than 10 years (let's simplify the condition as it is not relevant to the problem here) we set a column named RETIRED to value "YES" in the books table and delete the row (if present) in the content table.

PS:欢迎使用PL/SQL解决方案. (我的代码仍然在PL/SQL中)

PS: PL/SQL solutions are welcome. (my code is in PL/SQL anyway)

推荐答案

使用带有两个语句的一个事务:

Use one transaction with two statements:

将books更新为退休；
删除所有已淘汰书籍中的content.

Update books to retire;
Delete content of all the retired books.

PL/SQL解决方案可能是:

PL/SQL solution might be:

DECLARE
 TYPE id_list IS TABLE OF books.id%TYPE;
 retired_list id_list;
BEGIN
  UPDATE books
  SET retired = 'YES'
  WHERE publish_date <= add_months(TRUNC(SYSDATE), -120)
  RETURNING id BULK COLLECT INTO retired_list;

  FORALL id IN retired_list.FIRST .. retired_list.LAST
    DELETE content
    WHERE book_id = retired_list(id);

  COMMIT;
END;
/

这篇关于在多个表的同一SQL中进行UPDATE和DELETE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Books