CODEWITHSUNDEEP
sql
Bryan
Bryan

Reputation: 17581

SQl Delete top 100 from table

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)

Upvotes: 11

Views: 39302

Answers (5)

AdvanTiSS
AdvanTiSS

Reputation: 346

As for me, CTE is the better solution for ordered deletion 

;WITH records_delete AS (
    select top 103000 creation 
    from tRealtyTrac 
    order by creation)
DELETE records_delete

Upvotes: 4

backslash17
backslash17

Reputation: 5390

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.

Upvotes: 1

Brian
Brian

Reputation: 2221

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.

Upvotes: 22

ChrisLively
ChrisLively

Reputation: 88054

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. ;)

Upvotes: 2

Andy White
Andy White

Reputation: 88345

The inner query needs to be:

select top 103000 creation from ...

Upvotes: 4

Related Questions