Delete from table and insert same rows to other table

Is this a homework? It looks easy with two statements (one insert and one delete). Why you want to perform it with one statement ? What you have tried so far ?

If there is a way to delete from one table and to insert into another table in one statement, someone will post it. As far as I know that is not possible, but I don't know much.

In any case, if what I know (or I don't) is correct, you still need a way to do what you are required to do. Then it will be important to share why you wanted to do it all in one statement. "Do it all in one statement" is not a requirement, it is your solution to an actual requirement. Clearly part of it is to delete from one table and to insert into the other, but there are more conditions, right? (Or else you wouldn't think of doing it all in one statement.) So - what are these additional issues?

Are you concerned that by the time you insert two rows in the "other" table, someone has already added one more row with category 'BOOK' to "this" table, and now if you delete all books there's one row you are deleting that wasn't added to the other table? Are you concerned about performance, having to read the first table twice, and perhaps it's a very large table? What else? Why do you want to know if it can be done in one statement?

Cheers - mathguy

Mahesh

Why not you using Trigger . When you delete data from PRODUCT_CATEGORY table it will be inserted in PRODUCT_CAT_BOOKS table.

[ Moderator Action - Begging for points removed - See: Community Posting Etiquette ]

You could use PL/SQL to return the rows you delete into a collection that could then be inserted into your other table.

Hmmm - maybe better the other way around? Let me rephrase what you said above

You could use PL/SQL to return the 'ROWIDs' of the rows you 'insert' into a collection that could then be 'deleted' from your table.

1. better performance since you don't need to move the actual data twice

2. better, and much less, use of expensive PGA memory

A real use case might need to take other users into account as well as large numbers of rows.

1. create a select for update cursor to lock the rows

2. use a temp table to hold the ROWIDs

3. use a  multitable insert to insert the data into the data table and the ROWIDs into the temp table

4. use a delete based on the ROWIDs in the temp table

5. commit to release the locks

Of course that still doesn't prevent someone from inserting a new row for 'books'.

Can it be done using singe statement ?

No

Or can it be done using Merge statement.

 

No

See Andrew's reply and my comments to it.

why not OP create a trigger Before Delete ( or use When Deleting ) on table PRODUCT_CATEGORY to insert into another table ? and just issue a single statement to delete row on his condition(s).

Trigger would be slow (row by row insert) .

I use PL/SQL for moving data from one table to another and prefer rp0428 approach. I believe when he say "temp table" he mean plsql collection.

Trigger would be slow (row by row delete) .

I guess you mean row by row insert (into target table).

And this could be solved like this: use an after row action that populates a collection combined with an after statement action that inserts the collection to the target table with a bulk operation both combined in a compound trigger.

I believe when he say "temp table" he mean plsql collection.

No - when I said "temp table" that is exactly what I meant. Those 5 steps were listed AFTER I said this:

A real use case might need to take other users into account as well as large numbers of rows.

You don't want to use unlimited amounts of PGA memory even for ROWIDs.

The FIRST example I gave used the collection. But it only used the collection for ROWIDs - not for the entire rows as Andrews example did.

If you know the approximate number of ROWIDs and it is sufficiently small by all means just use a collection.

Yes I meant Insert Fixed Typo

I wrote a blog note about the problem shortly after the question appeared, and someone has just supplied an example of how to handle it with an approach structured like your second suggestion, but using a pipelined function to do the select for update - which should simultaneously minimise memory usage while maximising performance: http://jonathanlewis.wordpress.com/2016/11/22/deleteinsert/#comment-90532

Regards

Jonathan Lewis

Of course that still doesn't prevent someone from inserting a new row for 'books'.

Those rows are CALLED phantoms and it has nothing to do with the OP's problem statement.

https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1312

Phantom reads

A transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire. More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.