Deleting records using a cursor

Question

I have limited knowledge of SQL so can someone let me know if my thoughts are correct. I have a table that slowly fills up over time. I need to delete records from the first record to a given point in the table. As the primary key is based on GUIDs I am aware that I can’t do a delete easily because I can’t sort based on GUIDs (this was discussed in a previous thread). Would it be possible to use a cursor to look at the first record in the table and progress through the table deleting records until I get to a certain GUID ? The problem I have is that I need to delete all the records that were put in the table before the one defined by the GUID but I can only delete them if the data of the record is older that a predefined date. I know this is all a bit messy but I’m having to retro fit a solution so a bodgy solution will do for now.

I'm using MS SQL Server 2008

Answer 1

First of all, you have to wonder if you really want to use a cursor when you work with any kind of SQL query.

SQL is set based, so it is very efficient and capable of handling 'sets' of data.

First things first, what is your definition of 'the first record' ? How do you determine whether a record comes before another record ?

I understand that you have a 'datetime' column defined in your table ?

Is it possible to determine the 'datetime' of the record that you still want to keep in the table ?

If so, I think you can simply perform a DELETE on the table with a WHERE clause that makes sure that records that are older then the specified date, are not deleted.

Answer 2

Assuming the GUID is how you find out how far to delete:

DELETE FROM TheTable WHERE DateColumn < (SELECT DateColumn FROM TheTable WHERE GuidColumn = 'GUIDGUID-GUID-GUID-GUID-GUIDGUIDGUID')