0

Based on the solution here I tried using:

CREATE EVENT delete_expired_101
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 24 HOUR DO
DELETE FROM my_table WHERE id = 101;

on my php query, but it doesn't seem to work and gives me invalid syntax. Can you recommend me a way for this?

Take note I am using MS SQL not MySQL.

Community
  • 1
  • 1
CudoX
  • 985
  • 2
  • 19
  • 31
  • That is not compatible with T-SQL. You will have to build a process to delete data based on a timestamp which is stored as a column on the table. This could simply be a stored procedure that purges data over 24 hours old and called by a SQL Agent job. – Chris H Jun 06 '13 at 17:01
  • so by that you mean i have to use sql agent job instead? X_X – CudoX Jun 06 '13 at 17:05
  • if so, would you at least give me the right detailed procedures on doing this for me? i would really appreciate that. – CudoX Jun 06 '13 at 17:07
  • 1
    Simple as: DELETE FROM my_table WHERE DateValue >= DATEADD(hh,-24, GETDATE()) The table should we be created like: CREATE TABLE my_table ( DateValue DATETIME DEFAULT GETDATE(), Column1, Column2, . . ) – Chris H Jun 06 '13 at 17:11

2 Answers2

1

You need to use SQL Agent. The logic could be a SQL Agent job that is fired every 24 hours that deletes any data that has a datetime less than DATEADD(d,-1,getdate()). Please do some research on how to implement SQL Agent. If you need help with the code to delete data in a table based on a datetime value, please open a new question that includes that table's schema.

tommy_o
  • 3,640
  • 3
  • 29
  • 33
  • There are other ways to run code against a SQL Server database, but not by creating an "event" – tommy_o Jun 06 '13 at 17:13
  • I might choose to use DELETE script instead by adding a column as the initial timestamps will be stored. Thanks for all the suggestions. – CudoX Jun 06 '13 at 17:41
0

Ill be choosing @Chris H's suggestion by using delete function and set a table column which records timestamps.

Thanks for all the help

CudoX
  • 985
  • 2
  • 19
  • 31