Topic
  • 1 reply
  • Latest Post - ‏2013-03-14T10:50:12Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic Need help to delete old data from table

‏2013-03-14T03:12:53Z |
Can someone help me how to delete old data from table in database. I want to delete the data that more than 31 days from the table. I dont know what command I should use.
Updated on 2013-03-14T10:50:12Z at 2013-03-14T10:50:12Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Need help to delete old data from table

    ‏2013-03-14T10:50:12Z  
    Hi,

    By default, DB2 does not store information in the tables about when the rows were inserted or updated. It really depends on your database design/model, and you must have a date reference column in order to do what you want.

    For inserted rows:
    Is there any column with a date or timestamp value on it that registers when it was inserted?
    For updated rows:
    Is there any column with a date or timestamp value on it that registers when it was updated?
    For accessed rows, there is not any option.

    If you have a column with that information, first you write a select to preview the data, and once you have the values that you want to delete, transform the select into a delete.

    
    select * from table1 where days (current timestamp) - days(ins_date) >= 31 delete from table1 where days (current timestamp) - days(ins_date) >= 31
    


    In order to allow this functionallity in the future, you can use Time Travel. This feature will store automatically when a row was inserted and updated.