13 Replies Latest reply on Dec 12, 2016 1:50 PM by Jarkko Turpeinen

Delete from table and insert same rows to other table

Mahesh25 Nov 13, 2016 8:01 AM

Hi,

I have a table with rows as below.

CREATE TABLE PRODUCT_CATEGORY (

CATEGORY_ID NUMBER(15, 0) NOT NULL ENABLE

,CATEGORY_PARENT_ID NUMBER(15, 0)

,CATEGORY_NAME VARCHAR2(30 BYTE) NOT NULL ENABLE

,CATEGORY_DESCRIPTION VARCHAR2(50 BYTE)

,LANGUAGE_CODE VARCHAR2(5 BYTE)

)

;

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (1,null,'Media','Books, Music, and Movies','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (2,null,'Office','Office Supplies','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (3,null,'Electronics','Consumer Electronics','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (4,3,'Audio and Video','Audio and Video','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (5,3,'Camera and Photo','Camera and Photo','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (6,3,'Cell Phones','Cell Phones and Service','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (7,3,'Games','Computer and Video Games','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (8,1,'Books','Books','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (9,1,'DVDs','DVDs','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (10,1,'Periodicals','Magazines and Newspapers','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (11,1,'Music','CDs','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (12,2,'Hardware','Computers','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (13,2,'Supplies','Office Supplies','EN');

Insert into PRODUCT_CATEGORY (CATEGORY_ID,CATEGORY_PARENT_ID,CATEGORY_NAME,CATEGORY_DESCRIPTION,LANGUAGE_CODE) values (14,2,'Software','Software','EN');

What i want to do is to delete rows from table where it matches condition

upper(CATEGORY_DESCRIPTION) like '%BOOK%'

At the same time i want these rows to be inserted into other table .

So it wil be deleting 2 rows and also should be inserting those rows in PRODUCT_CAT_BOOKS table which has same table format.

Can it be done using singe statement ?

Or can it be done using Merge statement.

Regards,

Mahesh

1. Re: Delete from table and insert same rows to other table

Saubhik Nov 13, 2016 8:13 AM (in response to Mahesh25)

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 ?
Like Show 0 Likes(0)

Actions
2. Re: Delete from table and insert same rows to other table

mathguy Nov 13, 2016 6:35 PM (in response to Mahesh25)

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
Like Show 0 Likes(0)

Actions
3. Re: Delete from table and insert same rows to other table

MOJIBUL HOQUE Nov 14, 2016 9:09 AM (in response to Mahesh25)

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 ]
Like Show 0 Likes(0)

Actions
4. Re: Delete from table and insert same rows to other table

AndrewSayer Nov 13, 2016 1:42 PM (in response to Mahesh25)

You could use PL/SQL to return the rows you delete into a collection that could then be inserted into your other table.
Something like:
declare
type ttProductCategory is table of product_category%rowtype;
delRows ttProductCategory;
begin
delete product_category where upper(category_description) like '%BOOK%'
returning category_id,category_parent_id,category_name,category_description,language_code bulk collect into delRows;
forall i in 1..delRows.count
insert into product_cat_books
values delRows(i);
end;
/

I've made a demo on live sql using your sample: https://livesql.oracle.com/apex/livesql/s/d47tpqo35w79qy90r88e9yrtm
1 person found this helpful
Like Show 2 Likes(2)

Actions
5. Re: Delete from table and insert same rows to other table

rp0428 Nov 13, 2016 3:50 PM (in response to AndrewSayer)
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'.
Like Show 0 Likes(0)

Actions
6. Re: Delete from table and insert same rows to other table

rp0428 Nov 13, 2016 3:51 PM (in response to Mahesh25)

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.
Like Show 0 Likes(0)

Actions
7. Re: Delete from table and insert same rows to other table

Ahmed Haroon Nov 14, 2016 4:41 AM (in response to Mahesh25)

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).
Like Show 0 Likes(0)

Actions
8. Re: Delete from table and insert same rows to other table

Nimish Garg Nov 14, 2016 9:19 AM (in response to Ahmed Haroon)

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.
Like Show 1 Likes(1)

Actions
9. Re: Delete from table and insert same rows to other table

RogerT Nov 14, 2016 7:28 AM (in response to Nimish Garg)

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.
Like Show 0 Likes(0)

Actions
10. Re: Delete from table and insert same rows to other table

rp0428 Nov 14, 2016 7:59 AM (in response to Nimish Garg)

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.
Like Show 0 Likes(0)

Actions
11. Re: Delete from table and insert same rows to other table

Nimish Garg Nov 14, 2016 9:20 AM (in response to RogerT)

Yes I meant Insert Fixed Typo
Like Show 0 Likes(0)

Actions
12. Re: Delete from table and insert same rows to other table

Jonathan Lewis Dec 12, 2016 1:19 PM (in response to rp0428)

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
Like Show 0 Likes(0)

Actions
13. Re: Delete from table and insert same rows to other table

Jarkko Turpeinen Dec 12, 2016 1:50 PM (in response to rp0428)

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.
Like Show 0 Likes(0)

Actions