Conditional Delete

Question

Conditional Delete

Eldred Mullany

SSC Veteran

Points: 280
More actions
October 19, 2006 at 1:00 am

#174712

Hi
Still a newbie at SQL, I am trying to delete rows from a table based on a boolean condition ie
delete from table
where
(select count(*) from table ) > 5000
or
(select datediff(mi,min(DateStamp),getdate()) from table ) >120

Running this query deletes all my rows in my table, my logic is incorrect, only want to delete certain rows. Please help.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ijaz SSCommitted Points: 1912 More actions · Answer 1

In you where condition you have'nt made the criteria for any field of the deleting table. If your where condition return true in any case it will delete all record.

If the DateStamp column exists in the table you are deleteing the record, then your Where class should be this

WHERE datediff(mi,min(DateStamp),getdate()) > 120..

When you post any entry make the clear requirement, tell the table stucture and exact what you wnat. rather than the other body assume. It will very helpfull to slove your problem and as well the guy responding you back.

Thanks

cheers

Eldred Mullany SSC Veteran Points: 280 More actions · Answer 2

Thanks ijaz, still don't seem to have the syntax right: here is my query. I am trying to say delete * rows from CustomPropertyIRemoteProcVersion if greater than 120 mins.

select RemoteProc, Version, BranchID, DateStamp

from CustomPropertyIRemoteProcVersion

where

Datestamp like datediff(mi,min(DateStamp),getdate())

group

by RemoteProc, Version, BranchID, DateStamp

having

count(DateStamp)>120

Where am I going wrong ?

ijaz SSCommitted Points: 1912 More actions · Answer 3

Hi,

You just need to apply the where class and hopefully it will work fine.

Syntax look like this.

DELETE FROM CustomPropertyIRemoteProcVersion

WHERE

datediff(mi,min(DateStamp),getdate()) > 120

I think this would slove your problem

Thanks

cheers

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

DELETE works on individual rows, you can't use a HAVING condition directly. You would have to join the table to a grouped subquery. This might also be written as WHERE EXISTS(subquery) instead of a join, or even as WHERE pk IN (SELECT pk FROM ...); the most efficient form is hard to predict without testing.

DELETE FROM MyTable t

INNER JOIN (

SELECT A, B, C

FROM MyTable

WHERE ...

GROUP BY A, B, C

HAVING COUNT(*) > 120

) s ON s.A = t.A AND s.B = t.B AND s.C = t.C

WHERE ...