Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Delete from table and insert same rows to other table

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
Best Answer
-
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'.
Answers
-
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.
-
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
-
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.