Deleting

Data Modeling
Enthusiast

Deleting

Hi all - 

I have a scenerio i need assistance with. My scenerio is as follows:

I am building a snapsot table that assigns a batchid everytime it is run. These Id's are NOT necessarily in sequential order, but are always of a numeric type (BIGINT). -- Example: 900,001 then 900,020 then 900,101 etc....

I want to write a delete statement that looks at these and deletes only the oldest, keeping the last 5. With them not being in sequential oreder, what is a good way to do that?

Thanks for everyones input. Please let me know if there is any further clarification needed :)

Dan

Tags (2)
2 REPLIES
Enthusiast

Re: Deleting

Dan,

In your examples the value of id always increases.

If this is the case something like this will work to identify the ones you want to delete.

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 3936 StartFragment: 314 EndFragment: 3904 StartSelection: 314 EndSelection: 314

CREATE VOLATILE TABLE top5  
(
Adt_id BIGINT) PRIMARY INDEX (adt_id) ON COMMIT PRESERVE ROWS;

INSERT INTO top5
SEL TOP 5 adt_id FROM Youtable ORDER BY adt_id DESC;

SEL Yourtable.adt_id WHERE adt_id NOT IN (SEL adt_id FROM top5);

RGlass

Enthusiast

Re: Deleting

Dan,

if duplicate ID is allowed then you can use RANDOM() function with BIGINT range.

CREATE VOLATILE TABLE tbl_name

( Adt_id BIGINT,time_stamp timestamp(6)) PRIMARY INDEX (adt_id) ON COMMIT PRESERVE ROWS;

if you add timestamp column also then you can sort and delete result based on that column

INSERT INTO tbl_name

SEL random(1, 9223372), current_timestamp  

now you can delete the records

delete from tbl_name where time_stamp<date-5