Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Delete from table and insert same rows to other table

Mahesh25Nov 13 2016 — edited Dec 12 2016

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

This post has been answered by unknown-7404 on Nov 13 2016
Jump to Answer

Comments

rickepm

does this cover Jython?

Bob Rhubart-Oracle

does this cover Jython?

Are you asking about the sample chapter or the book? @"TonyScalese" wrote the chapter, and most of the book's authors participate in this community. So you can put the question to them directly.

TonyScalese

The chapter discusses the Software Development Lifecycle and specifically how LCM can be used.  The book discusses Jython but is not an in depth exploration of it as that is a topic to which an entire book (or more) should be dedicated.

rickepm

The chapter discusses the Software Development Lifecycle and specifically how LCM can be used.  The book discusses Jython but is not an in depth exploration of it as that is a topic to which an entire book (or more) should be dedicated.

is this a hands-on kind of book?

Sudhakar S

The chapter discusses the Software Development Lifecycle and specifically how LCM can be used.  The book discusses Jython but is not an in depth exploration of it as that is a topic to which an entire book (or more) should be dedicated.

Dear Tony,

I am planning to purchase this book to extend my knowledge on FDMEE and I am looking for the PDF version.

When i checked in Amazon it is in paperback format. If it is in ebook format it will be more good for me.

Can you please let me know if it available So that i can purchase.

Thanks,

Sudhakar S

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 9 2017
Added on Nov 13 2016
13 comments
30,344 views