SQl Delete top 100 from table

Question

I am trying to delete the all but the most recent 3,000 items in a table. The table has 105,000 records.

I am trying this, but an error is generated incorrect syntax.

delete tRealtyTrac where creation in( select top 103000 from tRealtyTrac order by creation)

Answer 1

The inner query needs to be:

select top 103000 creation from ...

Answer 2

A super easy way to do this:

1. select top 3001 from tRealtyTrac order by creation desc

2. take the last one's date then delete tRealtyTrac where creation < 'thedateyoufound'

But Andy has a good idea too. ;)

Answer 3

The delete syntax is going to be slightly different from what you have. An example would be:

DELETE FROM tRealtyTrac
WHERE creation in( select top 103000 creation from tRealtyTrac order by creation)

Notice how there is the "from" keyword. This is saying we want to delete from the table called tRealtyTrac

The one problem I foresee with this is, you are probably going to want to not use creation...

Instead:

DELETE FROM tRealtyTrac
WHERE someuniqueidcolumnlikeakeyofsomesort in( select top 103000 someuniqueidcolumnlikeakeyofsomesort from tRealtyTrac order by creation)

Otherwise you may delete more than you intended.

Answer 4

Try this:

DELETE FROM tRealtyTrac WHERE creation IN (SELECT top 103000  * FROM tRealtyTrac ORDER by creation)

You forgot the fields in tRealtyTrac (I used an asterisk to select all but you can make a list of them or only one). You also forgot the FROM clause.