drop table product_category

drop table product_cat_books

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

CREATE TABLE PRODUCT_CAT_BOOKS  (
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)
)

begin
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');
end;

select * from product_category

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;

select * from product_category

select * from product_cat_books