CODEWITHSUNDEEP
sqloracle-database
RobLaw0304
RobLaw0304

Reputation: 33

Insert then delete rows out of an oracle table

I wanted to see if there is a better way to do this. The statement takes all rows that happened further out than 6 months and puts them into an archive table. Then it takes all the ids present in both tables and removes them from the main table. Any Ideas?

INSERT INTO ArchiveTable
    SELECT *
    FROM MainTable
    WHERE DateItHappened < (SYSDATE - 180);

DELETE FROM MainTable a
   WHERE a.ID IN (
   SELECT b.ID 
   FROM ArchiveTable b 
   JOIN MainTable a
     ON b.ID = 
        a.ID);

Update:

I went ahead and implemented Joe's suggestion below to make this the final code. If anyone has any changes that should be made please let me know.

INSERT INTO ArchiveTable
    SELECT *
    FROM MainTable
    WHERE DateItHappened < (SYSDATE - 180);

DELETE FROM maintable a
    WHERE EXISTS (SELECT 1         
     FROM archivetable b
    WHERE a.id = b.id)

Upvotes: 1

Views: 3290

Answers (3)

OlleR
OlleR

Reputation: 272

In SQL Server it would be like, but oracle does not have a built in method to do this:

delete m
output deleted.ColumnA, deleted.ColumnB
into ArchiveTable
from 
MainTable m
where DateItHappened < (SYSDATE - 180);

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52386

My usual approach is to use a global temporary table to store the rowid's of the rows that I'm moving from the source table to the target table, then using that set to perform the deletes. It makes it 100% safe for such issues as an unstable initial data set.

insert all
  into archivetable (col1, col2 ...)
  values (col1, col2 ...)
  into maintable_deletes (delete_rowid)
  values (rowid)
select rowid, *
from   maintable;

delete from maintable
where  rowid in (select delete_rowid from maintable_deletes);

commit;

The GTT is useful because it can delete its own rows on commit.

Upvotes: 1

Joe
Joe

Reputation: 6827

The DELETE can be simplified:

DELETE FROM maintable a
 WHERE EXISTS (SELECT 1
                 FROM archivetable b
                WHERE a.id = b.id)

Upvotes: 1

Related Questions