Very Basic DELETE FROM query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very Basic DELETE FROM query

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-04-05 : 15:19:01

Hi All -

I have the following table


TABLE1

SERIAL_NUM DATE
123456 03/02/2012
123457 03/23/2012
123458 03/14/2012
123459 03/26/2012

There's about 4000 records.

I need to delete most recent 800 records based on the 'date' column.
Do I use a loop for this?


DELETE FROM TABLE1
WHERE .... ??

THANKS!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-05 : 15:31:09

[code];With cte as
(select top 800 * from Table1 order by [Date] desc)
delete from cte;[/code]I haven't tested it, so please test on a harmless table, especially the desired ordering by date etc., if you choose to use this approach.

marek_gd
Starting Member

6 Posts

Posted - 2012-04-05 : 15:33:02

There is no need to use a loop. Instead of it it is easier to use ranking function. Just add another column with number according to date in descending order.

SELECT SERIAL_NUM, DATE, DENSE_RANK() OVER (ORDER BY DATE DESC) AS NO into #table1
FROM yourtable

Now just delete top 800 records ( NO=1 is the most recent record -> descending order):

DELETE FROM #table1 WHERE NO<=800

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-05 : 15:42:20

quote:
Originally posted by marek_gd

There is no need to use a loop. Instead of it it is easier to use ranking function. Just add another column with number according to date in descending order.

SELECT SERIAL_NUM, DATE, DENSE_RANK() OVER (ORDER BY DATE DESC) AS NO into #table1
FROM yourtable

Now just delete top 800 records ( NO=1 is the most recent record -> descending order):

DELETE FROM #table1 WHERE NO<=800

To the OP: If you choose to use Marek's method, you may be better of using RANK function rather than DENSE_RANK. If you use DENSE_RANK, there is the possibility that you may end up deleting more than 800.

Also, if your objective is to delete from the source table rather than the temp table, you would need to modify the code appropriately.

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-04-05 : 15:53:19

Hi Sunita - Using a VIEW worked like charm!

Hi Marek - This didn't work. Using a DENSE_RANK deleted all the rows. Using RANK deleted 1081 rows.

Thanks for all your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 15:55:12

quote:
Originally posted by funk.phenomena

Hi Sunita - Using a VIEW worked like charm!

Hi Marek - This didn't work. Using a DENSE_RANK deleted all the rows. Using RANK deleted 1081 rows.

Thanks for all your help!

I would like to see how it deleted all rows

can you show query with dense_rank?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-04-05 : 16:01:07

[code]
SELECT [COUPON SERIAL NUMBER],[SUBMISSION DATE], RANK() OVER (ORDER BY [SUBMISSION DATE] DESC) AS NO into #table1
FROM #TEMP

DELETE FROM #table1 WHERE NO<=800

(1081 row(s) affected)
[/code]

Using Dense_Rank:

[code]
SELECT [COUPON SERIAL NUMBER],[SUBMISSION DATE], DENSE_RANK() OVER (ORDER BY [SUBMISSION DATE] DESC) AS NO into #table1
FROM #TEMP

DELETE FROM #table1 WHERE NO<=800

(5854 row(s) affected)

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 16:05:11

Ok..that does mean those 1081 records belonged to top 800 date values

So was requirement just to delete top 800 records or is it

deleting records with top 800 date values?

rank,dense_rank solutions are for latter scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-04-05 : 16:08:48

I needed it to Delete the TOP 800 records, based on the most recent DATE.

Sounds like it deleted the Top 800 date values instead.

Nonetheless, using WITH did the trick!

marek_gd
Starting Member

6 Posts

Posted - 2012-04-05 : 16:09:37

quote:

Originally posted by funk.phenomena


SELECT [COUPON SERIAL NUMBER],[SUBMISSION DATE], RANK() OVER (ORDER BY [SUBMISSION DATE] DESC) AS NO into #table1
FROM #TEMP

DELETE FROM #table1 WHERE NO<=800

(1081 row(s) affected)

Using Dense_Rank:


SELECT [COUPON SERIAL NUMBER],[SUBMISSION DATE], DENSE_RANK() OVER (ORDER BY [SUBMISSION DATE] DESC) AS NO into #table1
FROM #TEMP

DELETE FROM #table1 WHERE NO<=800

(5854 row(s) affected)

In case of dense_rank it is probably because there are multiple times when specified date occurs. For instance when you have 4 records with the same date then all these four records will have the same number NO. In that way you can delete more than 800 records. DENSE_RANK could be applicable only when each record has a different date.

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-04-05 : 16:10:40

The SUBMISSION DATE is a DATETIME value, alot of the records share the exact same Date and Time.
That's probably why!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 18:43:00

quote:
Originally posted by funk.phenomena

The SUBMISSION DATE is a DATETIME value, alot of the records share the exact same Date and Time.
That's probably why!

tht was my point

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources