All Forums Data Modeling
danimaltex 8 posts Joined 10/14
05 Jan 2015
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

Thanks, Dan
Glass 225 posts Joined 04/10
08 Jan 2015

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

Anilnandala 16 posts Joined 03/14
31 Mar 2015

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

You must sign in to leave a comment.