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 :
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 )