Skip to content
Advertisement

How to delete only Top 100 rows which are older than 30 days from current date in C#? [closed]

When I execute following query , it is executed properly in SSMS but same query in code does not delete anything and 0 rows are affected from thousands of rows.

Code is excuted without any error , have tried Query parameters as well but not worked.

Logs is table and DateCreated is column of DateTime. Sql Connection settings and other things are correct.

SSMS query execution below :

enter image description here

Can someone please correct this query for code ?

                connection.Open();                                       
                SqlCommand cmd1 = new SqlCommand("delete TOP (100) from [Logs] WHERE DateCreated < GETDATE() - 30", connection);
          
                var number_of_rows_deleted = cmd1.ExecuteNonQuery();
                result = number_of_rows_deleted.ToString() + " records deleted";

Advertisement

Answer

When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement

your sql script should be like this

DELETE from [Logs] 
WHERE Id IN  
 (SELECT TOP(100) Id
   FROM Logs
   WHERE  DATEDIFF(day, DateCreated, GETDATE()) < 30
  Order By DateCreated )
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement