UPDATE and DELETE in the same SQL in multiple tables?

Question

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

I tried various forms of MERGE , but I get either:

ORA-01446: cannot select ROWID from or pattern, view with DISTINCT, GROUP BY etc.

(probably caused by UNION ALL in the view)

or

ORA-38106: MERGE is not supported in the view or view of a connection to an INSTEAD OF trigger.

This ("view join") assumes that specifying two tables in any form is no-go for MERGE . It is right?

(reasons for using one operator: performance, consistency and clarity)

My Oracle DB has version 11.2.

The real problem is this:

We (the library) have a (parent) table of books and a (child) table of contents (one or zero per book, it has an FK in the table of books). Every year we start a work that for each book older than 10 years (let it simplify the condition, since it is not related to the problem here), we set a column with the name RETIRED for the value "YES" in the table of books and delete the line (if present) in the table content.

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

Answer 1

Use transaction one with two statements:

• Refresh books for retirement;
• Delete the content all lagging books.

A PL / SQL solution could 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; /