delete an certain amount - 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 2008 Forums

Transact-SQL (2008)

delete an certain amount

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2011-06-17 : 11:33:06

Hi all,

I got a DB with

id (int auto numbering)
sitename (varchar 400)
textblock (nvarchar 4000)

The problem is i got to many records (textblocks) for each site.
I want to delete so that i only have 30 for each site left.

how do i do that?

thanks a lot

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 11:56:24

delete tbl
from tbl t1
where id not in (select top 30 id from tbl t2 where t1.sitename = t2.sitename order by id)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike13
Posting Yak Master

219 Posts

Posted - 2011-06-17 : 15:49:54

thanks nigel worked

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-23 : 07:13:34

Hi,

I used a while ago and it worked but now i'm getting error

multi-part identifier "t1.sitename" could not be bound

Maybe a typo or () wrong or something
here is the statement:

delete dbo.T_TextBlocks
from dbo.T_TextBlocks t1
where id not in (select top 30 id from dbo.T_TextBlocks t2 where t1.sitename = t2.sitename order by id)

thanks a lot

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-23 : 07:18:06

delete ~~dbo.T_TextBlocks~~t1
from dbo.T_TextBlocks t1
where id not in (select top 30 id from dbo.T_TextBlocks t2 where t1.sitename = t2.sitename order by id)

--
Chandu

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-23 : 11:02:56

tnx, but didn't work
got these errors:

Error near identifier t1. Expecting OUTPUT.

i press continue and get

incorrect syntax near t1
and
incorrect syntax near keyword ORDER

with this sql:

delete from dbo.T_TextBlocks t1
where id not in (select top 30 id from dbo.T_TextBlocks t2 where t1.sitename = t2.sitename order by id)

any help please.

thanks a lot

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 10:37:03

try

delete t1.*
from dbo.T_TextBlocks t1
where t1.id not in (select top 30 id from dbo.T_TextBlocks t2 where t1.sitename = t2.sitename order by id)

Nick. ;)

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-24 : 10:42:51

tnx, but not working, says t1.* invalid object

here is the code to build the table

CREATE TABLE [dbo].[T_TextBlocks](
[id] [int] IDENTITY(1,1) NOT NULL,
[sitename] [nvarchar](255) NULL,
[textblock] [nvarchar](4000) NULL,
CONSTRAINT [PK_T_TextBlocks] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 10:50:28

mike, what version of sql are you using please?

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 10:50:56

I meant to say what version of sql server.

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 11:02:11

OK, so if you are using 2005+ then try:

WITH MyTbl
AS
(
select top 30 id
from dbo.T_TextBlocks t2 WITH (NoLock)
)
delete
from T_TextBlocks
where id not in (Select id from MyTbl)

we are using a CTE here. That is kinda habbit for me as I usually dont touch 2000/MSDE versions any more.

Hope that helps ;)

Nick.

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-24 : 11:24:17

nope didn't work

(0 row(s) affected)

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 12:22:28

mike, what version of sql server are you using?

How many records are currently in your table?

Are you seeing any errors generated, or are you just getting 0 rows affected?

As a note, I tried the query on a local copy of a test database and threw in some example rows (7 in total), then ran the query changing the 30 to a 3 in order to test deleting 3 records, and it deleted 4 rows as expected.

Nick.

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 12:40:48

I meant to say " ... then ran the query changing the 30 to a 3 in order to test deleting 4 records ...", must need more coffee today :)

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-24 : 13:50:55

sql 2008 and sql 2005

it like 1 million records
would it be beter to use a group by or something?

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-24 : 13:56:36

maybe i'm doing something wrong
what is MyTbl in the statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 14:18:01

[code]
delete t
from (select row_number() over (partition by sitename order by id desc) as seq
from tablename
)t
where seq>30
[/code]

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

nicklarge
Starting Member

40 Posts

Posted - 2012-10-24 : 14:33:38

mike, MyTbl is the equivalent of a temp table through the use of a CTE (Common table expression). In the example that I wrote it loads the 30 ID's into memory (with an alias of MyTbl), and deletes from the T_TextBlocks table those items that do not have the same ID's as those in the MyTbl table in memory; that is everything that is not the 30 rows that you wish to keep. This is just one way of doing it. BTW, did it work? Did you get an error?

Also, let us know please if you tried visakh's method and if so, whether it worked for you.

Nick.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 14:57:36

quote:
Originally posted by nicklarge

mike, MyTbl is the equivalent of a temp table through the use of a CTE (Common table expression). In the example that I wrote it loads the 30 ID's into memory (with an alias of MyTbl), and deletes from the T_TextBlocks table those items that do not have the same ID's as those in the MyTbl table in memory; that is everything that is not the 30 rows that you wish to keep. This is just one way of doing it. BTW, did it work? Did you get an error?

Also, let us know please if you tried visakh's method and if so, whether it worked for you.

Nick.

It wont work as OP expects Nick

Reason is you're just populating MyTbl with 30 random rows from table (keep in mind that unless you've an explicit ORDER BY you cant guarantee order of retrieval) and using it to retain data in main table which will just leave these 30 rows in it whereas what OP is asking is to retain 30 rows per each sitename value not table as a whole

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

mike13
Posting Yak Master

219 Posts

Posted - 2012-10-24 : 15:55:32

figured it out, this is working. thank everyone

delete t_textblocks
from t_textblocks t1
where id not in (select top 30 id from t_textblocks t2 where t1.sitename = t2.sitename order by id);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources