1 2 Previous Next 21 Replies Latest reply on Jul 26, 2017 5:25 PM by rp0428

    Moving rows to destination table and deleting from source in one go?

    805614eb-dada-4bac-8a92-c1e3b1a82a3a

      Is it possible to move rows from a source table to a destination table and then deleting the same rows from the source table in one statement ?

       

      Something like this would be ideal, but I know "DELETE" cannot be use in "WHEN NOT MATCHED" clause... any suggestion please?

       

      MERGE INTO destination_table d

      USING (select *

               from source_table

              where  conditions ) s

      ON (s.ID = d.ID)

      WHEN NOT MATCHED THEN

          INSERT (field1_d, field2_d)

          VALUES (fields_d, field2_s)

          DELETE WHERE 1 = 1;

       

      Thanks

        • 1. Re: Moving rows to destination table and deleting from source in one go?
          Solomon Yakobson

          Not possible in one statement in general. However, if target table is partitioned and you want to populate target table empty partition from source table (all rows in source table would belong to that partition) you could do partition exchange. Then source table rows would end up as corresponding partition in target table and source table would become empty since target partition initially was empty.

           

          SY.

          • 2. Re: Moving rows to destination table and deleting from source in one go?
            John Thorton

            805614eb-dada-4bac-8a92-c1e3b1a82a3a wrote:

             

            Is it possible to move rows from a source table to a destination table and then deleting the same rows from the source table in one statement ?

             

             

            What would be gained by doing it in 1 statement when compared to using 2 statements followed by a single COMMIT?

            • 3. Re: Moving rows to destination table and deleting from source in one go?
              BluShadow

              John Thorton wrote:

               

              805614eb-dada-4bac-8a92-c1e3b1a82a3a wrote:

               

              Is it possible to move rows from a source table to a destination table and then deleting the same rows from the source table in one statement ?

               

               

              What would be gained by doing it in 1 statement when compared to using 2 statements followed by a single COMMIT?

               

               

              Well, I guess some other session could go and insert (and commit) some other rows to the source between you moving them and then deleting, so then you'd end up deleting something that hasn't yet been transferred. 

              • 4. Re: Moving rows to destination table and deleting from source in one go?
                John Thorton

                BluShadow wrote:

                 

                John Thorton wrote:

                 

                805614eb-dada-4bac-8a92-c1e3b1a82a3a wrote:

                 

                Is it possible to move rows from a source table to a destination table and then deleting the same rows from the source table in one statement ?

                 

                 

                What would be gained by doing it in 1 statement when compared to using 2 statements followed by a single COMMIT?

                 

                 

                Well, I guess some other session could go and insert (and commit) some other rows to the source between you moving them and then deleting, so then you'd end up deleting something that hasn't yet been transferred.

                Please post reproducible test case that shows above can be actualized in reality.

                • 5. Re: Moving rows to destination table and deleting from source in one go?
                  mathguy

                  Careful with the MERGE statement!   The issue isn't that you can't have DELETE in the WHEN NOT MATCHED branch. The issue is that all modification are made to the destination table. Even if you could DELETE in the WHEN NOT MATCHED branch, you would be deleting from the destination table - not from the source!

                   

                  What Solomon Yakobson explained is over my head (I don't know anything about partitioned tables). However, what I think is true is that "standard" DML (UPDATE, INSERT, DELETE, MERGE) only operate on one table per statement - you can't affect two different tables in the same statement. Regardless of any other restrictions.

                  • 6. Re: Moving rows to destination table and deleting from source in one go?
                    BluShadow

                    John Thorton wrote:

                     

                    BluShadow wrote:

                     

                    John Thorton wrote:

                     

                    805614eb-dada-4bac-8a92-c1e3b1a82a3a wrote:

                     

                    Is it possible to move rows from a source table to a destination table and then deleting the same rows from the source table in one statement ?

                     

                     

                    What would be gained by doing it in 1 statement when compared to using 2 statements followed by a single COMMIT?

                     

                     

                    Well, I guess some other session could go and insert (and commit) some other rows to the source between you moving them and then deleting, so then you'd end up deleting something that hasn't yet been transferred.

                    Please post reproducible test case that shows above can be actualized in reality.

                     

                    I would have thought a man of your calibre would be able to do that already. 

                     

                     

                    If you insist though...

                    Two tables src and tgt...

                     

                    SQL> select * from src;

                            N
                    ----------
                            1
                            2
                            3

                     

                    SQL> select * from tgt;

                     

                    no rows selected

                     

                    Session 1, does the first of it's two statements to insert the rows from source to target...

                     

                    SQL> insert into tgt select * from src;

                     

                    3 rows created.

                     

                    Session 2, inserts and commits some data to the source...

                     

                    SQL> insert into src (n) values (4);

                     

                    1 row created.

                     

                    SQL> commit;

                     

                    Commit complete.

                     

                    SQL> select * from src;

                             N
                    ----------
                             4
                             1
                             2
                             3

                     

                    Session 1, deletes the data from source (on the belief that it's transferred all the source data to target), and commits...

                     

                    SQL> delete from src;

                     

                    4 rows deleted.

                     

                    SQL> commit;

                     

                    Commit complete.

                     

                    Which has deleted all 4 rows, including the new data that session 2 inserted.

                    The target table still only has the 3 initial rows of data...

                     

                    SQL> select * from tgt;

                             N
                    ----------
                             1
                             2
                             3

                     

                    Of course we could have conditionally deleted, only the data that existed in the target, but if you're dealing with millions of rows, then that's more time consuming, or we could lock the source table until we've completed our two statements.  These however are the things to be aware of and deal with correctly rather than the OP's original concept of doing it all in one go (which of course you can't).  So, the original question of "can this be done in 1 statement" is not a stupid question... there would be something gained by having an SQL statement that could effectively move rows between tables as a single statement.  MERGE is great if you want to do things in one go to a target table, but doesn't quite allow for deletions of the source.  I could see a valid future enhancement being to allow the DELETE option on the "when not matched" part of MERGE to delete source rows where they are successfully inserted to the target.

                    • 7. Re: Moving rows to destination table and deleting from source in one go?
                      Stew Ashton

                      mathguy wrote:

                      ...what I think is true is that "standard" DML (UPDATE, INSERT, DELETE, MERGE) only operate on one table per statement - you can't affect two different tables in the same statement...

                      INSERT ALL and INSERT FIRST can operate on multiple target tables.

                       

                      Best regards, Stew

                      • 8. Re: Moving rows to destination table and deleting from source in one go?
                        Stew Ashton

                        BluShadow wrote:

                        ...

                        Well, I guess some other session could go and insert (and commit) some other rows to the source between you moving them and then deleting, so then you'd end up deleting something that hasn't yet been transferred.

                        To avoid that problem, but using two SQL statements:

                         

                        --drop table t purge;
                        create table t as select level n from dual connect by level <= 10;
                        --drop table u purge;
                        create table u(n number);
                        --drop table tmp purge;
                        create global temporary table tmp(rid rowid);
                        
                        insert all
                        into u values(n)
                        into tmp values(rid)
                        select n, rowid rid from t
                        where mod(n,2) = 0;
                        
                        delete from t where rowid in (select rid from tmp);
                        commit;
                        
                        select count(*) "count(*) from TMP" from tmp;
                        
                        count(*) from TMP
                        -----------------
                                        0
                        
                        select n "T rows" from t;
                        
                            T rows
                        ----------
                                 1
                                 3
                                 5
                                 7
                                 9
                        
                        select n "U rows" from u;
                        
                            U rows
                        ----------
                                 2
                                 4
                                 6
                                 8
                                10
                        

                         

                        Warning: this solution could suffer from "lost updates". The rows will be moved as they were at the moment the INSERT ALL started. If any changes are made to the selected T rows after the start of the INSERT statement, those changes will be lost.

                         

                        To avoid that problem, locking the T table is the safest thing to do. If that is not possible, precede the INSERT ALL statement with a SELECT FOR UPDATE statement.

                         

                        Best regards, Stew

                        • 9. Re: Moving rows to destination table and deleting from source in one go?
                          BEDE

                          Not so complicated, and it is the best way to populate a table partition quickly - that is faster than an insert select. Suppose you have a staging table where you prepare the data that is meant to be "transferred" to a partition of a table. Typically that would be the scenario for a DB warehouse that has a table partitioned by business_date, and, on closing a business date there are some jobs performing some processing and populating the staging table, than doing exchange partition.

                          So, you have, say acct_balances partition by range business_date. And you have the table stg_balances that is the staging table. Mind well: for exchange partition the two tables must have EXACTLY the same structure - meaning the same columns exactly in the same order (even a column that was set unused may prevent exchange partition form being performed) and exactly the same structures for indexes. And than you will alter table acct_balances exchange partition p_part_name with table stg_balances without validation.

                          At database level that is allocating segments from one database object to another database object without actually copying the data from one datablock to another: that is why it should be faster than insert select.

                          • 10. Re: Moving rows to destination table and deleting from source in one go?
                            BEDE

                            well, that sounds neat, only that I would lock table first or select for update, just to make sure some other session doesn't mess up the data I want to insert and delete

                            • 11. Re: Moving rows to destination table and deleting from source in one go?
                              Stew Ashton

                              BEDE wrote:

                               

                              well, that sounds neat, only that I would lock table first or select for update, just to make sure some other session doesn't mess up the data I want to insert and delete

                              Funny, I was updating my reply with exactly the same thought, and when I saved it there you were "pre-agreeing" with me

                               

                              Best regards, Stew

                              • 12. Re: Moving rows to destination table and deleting from source in one go?

                                Is it possible to move rows from a source table to a destination table and then deleting the same rows from the source table in one statement ?

                                No.

                                ... any suggestion please?

                                You need to design/use a SET operation to perform an INSERT and DELETE as one transaction.

                                 

                                That operation needs to ensure that the rows deleted are EXACTLY the same rows as those initially selected/inserted. You need to take into account the complications that can arise due to Oracle being a multi-user Database:

                                 

                                1. other users can lock, update or delete rows that your cursor may select since the SELECT part of your insert will NOT lock the row in the source table.

                                 

                                2. a row you 'copy' may have been altered/updated AFTER you copied it but before you delete it. That row could still be locked preventing you from deleting it until the lock is removed.

                                 

                                3. other users could insert new rows that WOULD HAVE BEEN copied by your INSERT ... SELECT if they had existed at the time. You have to be careful NOT to delete them. For example

                                INSERT INTO target SELECT * FROM source WHERE someDate = someValue;

                                DELETE from source WHERE someDate = someValue;

                                Another user could insert rows that have 'someDate = someValue' AFTER your INSERT ... SELECT query begins. The above would now delete those rows even though you haven't copied them.

                                 

                                4. rows can have their ROWID changed. A common method of achieving what you ask is to save the rowids of the rows you query and then delete the source rows that have those rowids. The problem arises if the rowid can change (a delete followed by an insert, or an update of a partitioning key that moves a row to a different partition).

                                 

                                One way is to use a SELECT ... FOR UPDATE to lock the rows so that other users can't alter or delete them. That will NOT prevent other users from inserting rows that meet a 'someDate = someValue' condition though.

                                 

                                In short - there is NO SIMPLE solution.

                                • 13. Re: Moving rows to destination table and deleting from source in one go?

                                  What would be gained by doing it in 1 statement when compared to using 2 statements followed by a single COMMIT?

                                  It would solve the MANY potential problems you can run into trying to use that '2 statement' method. See my reply to OP for details and a partial list.

                                   

                                  The complications arise when the second statement has to try to RE- IDENTIFY the same EXACT rows that were selected in the first statement.

                                   

                                  1. some rows may no longer exist

                                  2. new rows meeting the same condition may have been created - but wouldn't have been copied

                                  3. rowids of the rows may have changed

                                   

                                  There IS NO easy generic way to use two statements and get guaranteed results.

                                  • 14. Re: Moving rows to destination table and deleting from source in one go?

                                    So, the original question of "can this be done in 1 statement" is not a stupid question... there would be something gained by having an SQL statement that could effectively move rows between tables as a single statement.

                                    +1 - this is well worth an enhancement request IMHO.

                                     

                                    I frequently encounter such 'MOVE' operations in projects I work on and it is non-trivial to do them correctly as SET operations when restart/recovery has to be taken into account.

                                     

                                    As you mentioned there would be Read Consistency issues (selects don't lock rows although FOR UPDATE does) and well as 'phantom' issues. Using locks is the only way I know to 'flag' rows but that doesn't cover 'phantoms'.

                                     

                                    A couple of possibilities come to mind:

                                     

                                    1. a NEW sql statement: e.g. MOVE. Being new Oracle could implement it however they want without having to worry about any conflicts with how existing code works.

                                     

                                    2. a FOR MOVE option for the SELECT statement. This would be similar to 'for update' and would lock the rows.

                                     

                                    Although a FOR UPDATE locks the set of rows there isn't any way (that I know of) to then refer to that entire set of rows.

                                     

                                    Using a FOR MOVE would allow a 'pause' to be able to validate the rows being moved (selected, inserted, merged) to make sure the correct rows have been identified.

                                     

                                    Then you would probably need a mod to the DELETE statement that would delete rows locked by the FOR MOVE.

                                     

                                    I think a MOVE could be a great addition to the toolkit.

                                    1 2 Previous Next