Delete from table and insert same rows to other table

Mahesh25

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

Unknown

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

Saubhik

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 ?

mathguy

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

MOJIBUL HOQUE

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

AndrewSayer

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

Unknown

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

Unknown

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.

Ahmed Haroon

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

Nimish Garg

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.

RogerT

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.

Unknown

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.