Hello all,

I am doing some work on a customer database and need to delete specific date from a back up table. The data needs to be the top 10 based or date order. (Don't ask why :))

The following script does not work, but what could i use?

delete (select top 10 * from back_notifmsgqueue
order by timetosend desc)
from back_notifmsgqueue

If you mean delete the previous 10 days data from today in a table then the following would be do it...

delete from
back_notifmsgqueue
where
timetosend >= date_sub(curdate(), interval 10 day)

However, if you mean that you want to delete the most recent 10 days in a table and it has not been updated for a period of time then you will have to perform a subquery based upon your max(timetosend) to achieve the same

Use a sub-query as above, something like this:

DELETE FROM back_notifmsgqueue
WHERE <ID> IN (SELECT TOP 10 <ID> FROM back_notifmsgqueue
ORDER BY timetosend DESC)

Change the <ID> to whatever your primary key is called

Thats what I was trying to acheive in other examples, but without the IN.

Thanks StuP

Think I over complicated it ... DOH

Please use 'Begin Tran' before you do the delete, as I've just had to restore a 160GB DB back for a client because they did they a delete statement before checking what it would actually delete.

That is properly good advice. Don't commit the transaction until you're sure the query does what you want. Same goes for insert and update statements really. And for added safety and if the db is offline then take a backup before doing anything.