Forum Stats

  • 3,874,438 Users
  • 2,266,734 Discussions
  • 7,911,850 Comments

Discussions

Delete from table and insert same rows to other table

Mahesh25
Mahesh25 Member Posts: 47 Red Ribbon
edited Dec 12, 2016 8:50AM in SQL & PL/SQL

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

Bharat GAhmed Haroon

Best Answer

  • Unknown
    edited Nov 13, 2016 10:50AM 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'.

«1

Answers

  • Saubhik
    Saubhik Member Posts: 5,922 Gold Crown
    edited Nov 13, 2016 3:13AM

    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
    mathguy Member Posts: 10,900 Black Diamond
    edited Nov 13, 2016 1:35PM

    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
    MOJIBUL HOQUE Member Posts: 163 Bronze Badge
    edited Nov 14, 2016 4:09AM

    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
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 13, 2016 8:42AM

    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

    Bharat GAhmed HaroonMahesh25
  • Unknown
    edited Nov 13, 2016 10:50AM 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'.

  • Unknown
    edited Nov 13, 2016 10:51AM
    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
    Ahmed Haroon Member Posts: 1,137 Bronze Badge
    edited Nov 13, 2016 11:41PM

    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
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited Nov 14, 2016 4:19AM

    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.

    Ahmed Haroon
  • RogerT
    RogerT Member Posts: 1,860 Gold Trophy
    edited Nov 14, 2016 2:28AM
    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
    edited Nov 14, 2016 2:59AM
    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.

This discussion has been closed.