delete rows from table where ......

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

SQL Server 2000 Forums

SQL Server Development (2000)

delete rows from table where ......

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2007-06-13 : 01:45:39

I have one table which contains field name as createdate(getdate())

when new record inserts in table it automatically inserts date.

now i want that if there are more 5 reocrds in my table then it automatically delete all old records.only last 5 inserted records must be there in my table.

please show me how,

waitining for reply,

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-06-13 : 02:27:52

Let's say the name of the field that contains the date is AutoDate, the name of the table is TestTable, and that the name of the PK field is PKField.
You might try something like this:

CREATE TRIGGER testTrigger TestTable FOR INSERT
AS
BEGIN
IF (SELECT COUNT(*) FROM TestTable > 5)
BEGIN
DELETE FROM TestTable WHERE PKField = (SELECT TOP 1 PKField from TestTable ORDER BY AutoDate)
END
RETURN
END

Being a genius has its advantages...

pootle_flump

1064 Posts

Posted - 2007-06-13 : 02:36:11

What happens if more than one record is inserted?

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-06-13 : 02:49:19

Then instead of If clause, you should use While clause

i.e.

CREATE TRIGGER testTrigger TestTable FOR INSERT
AS
BEGIN
WHILE(SELECT COUNT(*) FROM TestTable > 5)
BEGIN
DELETE FROM TestTable WHERE PKField = (SELECT TOP 1 PKField from TestTable ORDER BY AutoDate)
END
RETURN
END

Alternative is:
CREATE TRIGGER testTrigger ON TestTable FOR INSERT
AS
BEGIN
DECLARE @TEMPFIELD INT
SELECT @TEMPFIELD = SELECT COUNT(*) - 5 FROM TestTable
DELETE FROM TestTable WHERE PKField in (SELECT TOP @TEMPFIELD PKFIELD FROM TestTable ORDER BY AutoDate)
RETURN
END

Being a genius has its advantages...

Kristen
Test

22859 Posts

Posted - 2007-06-13 : 03:23:50

Wouldn't this be easier?


CREATE TRIGGER MyTrigger ON MyTable
FOR INSERT
AS
BEGIN
    DELETE D
    FROM MyTable AS D
    WHERE createdate NOT IN 
          (
             SELECT TOP 5 createdate 
             FROM MyTable
             ORDER BY createdate DESC
          )
END

it will leave more than 5 records if there are duplicate values in the createdate column. If this is a problem the sub-select could pick just 5 PKs to match on instead.

Kristen

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-06-13 : 03:36:04

Maybe it would, maybe it would not. I am not saying that either solution is better, but every solution is welcome. You know how it is with engineers: if you have one problem and a hundred engineers, you will get (at least) a hundred solutions. Your solution is better because it keeps the records that were inserted last, and the advantage of the previous solution is that it leaves five records in the table by deleting the records from the beginning.
However, the solution is creative, and I like it.
The only thing I do not like about Kristen's solution is that the criteria for deletion is not PK. I think it is a good practice to delete records based on their PK, because that way one protects oneself from accidental deletion of records.

Being a genius has its advantages...

Kristen
Test

22859 Posts

Posted - 2007-06-13 : 07:51:37

"I think it is a good practice to delete records based on their PK"

Well not necessarily, but in order to have ONLY 5 rows left in the table, and assuming risk of duplicate values for createdate, then you need to use PKs anyway - as I indicated: "If this is a problem the sub-select could pick just 5 PKs to match on instead"

If you only have 5 rows in a table you aren't going to have performance problems whichever column you use as a criteria!

I wasn't keen on the multiple-SELECT-statement approach as that seemed to be aiming for something that as not going to be optimum.

This solves the problem of potentially duplicate createdate values, and uses PKs, so should be more efficient anyway.


CREATE TRIGGER MyTrigger ON MyTable
FOR INSERT
AS
BEGIN
    DELETE D
    FROM MyTable AS D
    WHERE MyPK NOT IN 
          (
             SELECT TOP 5 MyPK
             FROM MyTable
             ORDER BY createdate DESC, MyPK DESC
          )
END

note that you need MyPK in the ORDER BY as a tie-break to have repeated conditions. Up to you whether the Sequence for MyPK is Ascending or Descending - if MyPK values are added i a generally ascending sequence you should use Descending in the Order By. If they are random then choose one! just to ensure repeatability.

Kristen

pootle_flump

1064 Posts

Posted - 2007-06-13 : 08:46:59

If I may be picky - a non-nullable alternate key would suffice too

Oh - I'm not allowed to be picky? Ignore then....

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-06-13 : 10:48:48

Well, basically, it looks the same to me. The only difference is that in my solution records are ordered in ascending order and all but the last five are deleted, and in Kristen's solution, records are ordered in descending order, and all but the first five are deleted.

Being a genius has its advantages...

Kristen
Test

22859 Posts

Posted - 2007-06-13 : 13:14:56

"it looks the same to me"

Depends on whether there can be duplicate values for createdate.

It should also help performance if the number of SELECT etc. statements is reduced.

Kristen

pootle_flump

1064 Posts

Posted - 2007-06-13 : 13:24:43

Easiest of all would be to leave them all in there and only select top 5 IMHO but there you go. 5 is an awfully small number to limit a table to. You could have substantially more in there and not fill a data page.

EDIT - depending on the nature of the data... yadda yadda ya...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources