help with delete statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

help with delete statement

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-11-25 : 18:24:00

I have a table with two columns, until now users have been able to add as many searches as they like. However this is now slowing things down and I want to limit it to 100.

How can I delete every row beyond row 100 where users have a rowcount greater than 100? It's not mission critical data so I have no problem doing this.

Thanks alot,
mike123

Sample Data:

tblUserSearch

userID, searchID

500,3
500,35
500,43
..etc

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-25 : 19:28:14

That is an intersting problem...

Here is my crack at it...Deletes Searches with count greater than 2


create table tblUserSearch(userid int, search int PRIMARY KEY(userid, search))
GO
insert  tblUserSearch (userID, search)
	SELECT 500,35
	UNION ALL
	SELECT 500,43
	UNION ALL
	SELECT 500,32
	UNION ALL
	SELECT 400,31
	UNION ALL
	SELECT 400,34
	UNION ALL
	SELECT 400,35
GO
CREATE VIEW UserSearchRank
AS
SELECT UserID, search, (SELECT COUNT(*) FROM tblUserSearch WHERE USERID = T.UserID and search<= T.search) AS CNT
FROM tblUserSearch T
GO
DELETE T
FROM tblUserSearch T
INNER JOIN UserSearchRank usr on usr.UserID = T.UserID AND usr. search = T.search
WHERE usr.CNT > 2

DavidM

"SQL-3 is an abomination.."

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-25 : 19:52:47

With a cursor, this is easy. I'm trying to figure out a set based way to do this.

EDIT: Someone beat me to it. Nice!
I wonder if it can be done without the View...

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-25 : 20:01:18

Of course it can, simply substitute the view name with actual definition in the INNER JOIN.

I used the view to make it easier to read and understand...

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-25 : 20:01:42

The view makes it very readable, but it can be done in one statement

my question is: Is [search] unique for each userid?


delete tblusersearch 
from tblusersearch s
join 
	(
	select UserID, search, (SELECT COUNT(*) FROM tblUserSearch WHERE USERID = T.UserID and search <= T.search) CNT
	FROM tblUserSearch T
	) a
on s.userid = a.userid and s.search = a.search
where a.cnt > 2

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-25 : 20:01:43

Yep it can be done, but it's a bit messy looking:


CREATE TABLE #UserSearch(UserID INT, SearchID INT)

INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 1)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 2)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 3)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 4)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 5)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 6)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 7)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 8)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 9)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(500, 10)

INSERT INTO #UserSearch(UserID, SearchID) VALUES(600, 1)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(600, 2)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(600, 3)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(600, 4)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(600, 5)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(600, 6)


INSERT INTO #UserSearch(UserID, SearchID) VALUES(700, 1)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(700, 2)
INSERT INTO #UserSearch(UserID, SearchID) VALUES(700, 3)

DELETE us1 FROM #UserSearch us1 
INNER JOIN (SELECT us.UserID, us.SearchID, (SELECT COUNT(*) FROM #UserSearch WHERE UserID = us.UserID and SearchID <= us.SearchID) AS Cnt
FROM #UserSearch us) AS us2 ON us2.UserID = us1.UserID AND us2.SearchID = us1.SearchID
WHERE us2.Cnt > 2


SELECT * FROM #UserSearch

DROP TABLE #UserSearch

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-11-26 : 06:39:00

thanks alot guys! awesome response, and worked perfectly

and ehorn to answer your question no it is not unique

cheers!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources