Menu
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)
680
By default in SSMS, you can select 1000 Rows and Edit 200 Rows. If you would like to change the default value then go to SSMS > Tools > Options: In the Options dialog box, highlight SQL Server Object Explorer and change the default values to any number as per your requirements.
SQL delete statement and TOP clauseYou can use the TOP statement to delete the rows as well. For example, the below query deletes the top 100 rows from the Orders table.
DELETE FROM table_name WHERE column_name BETWEEN value 1 AND value 2; Another way to delete multiple rows is to use the IN operator. DELETE FROM table_name WHERE column_name IN (value 1, value 2, value 3, etc...); If you want to delete all records from the table then you can use this syntax.
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.
184
The inner query needs to be:
select top 103000 creation from ...
22
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
