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