In a table i need to delete some rows

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

In a table i need to delete some rows

Author

Topic

subashreddy
Starting Member

6 Posts

Posted - 2011-05-17 : 05:19:58

Can any one help me how to delete all rows except 4 rows..
Example: In a Employee table there are 100 records in that i want to delete 96 records and i want to keep 4 records in employee table..can any one help me please

subash

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-17 : 05:38:16

which four records(top, bottom.middle,...etc.,) you want to keep (out of 100)

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-17 : 05:42:28

Refere Examples from

http://msdn.microsoft.com/en-us/library/ms189835(v=SQL.90).aspx

--Ranjit

subashreddy
Starting Member

6 Posts

Posted - 2011-05-17 : 05:43:29

Thanks for your early reply,

i want to keep middle four records

subash

subashreddy
Starting Member

6 Posts

Posted - 2011-05-17 : 05:56:00

Ranjit,

Can u please help me,

--Delete from employee where

after that i what condition i need to keep,

I want to keep middle four records

please help me.

subash

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-17 : 06:21:01

Check This Example:

create table #temp
(
number int identity(1,1)
,name varchar(10)
)
go

-- Inserting 100 Records in a single batch
insert into #temp(name) values('aaaaa')
go 100

select * from #temp
go

--Deleting records using row_number() function
with cte as
(
select number,name,row_number() over(order by number)as serialNum from #temp
)delete from cte --deleteing records
where serialnum not in (49,50,51,52)

--Ranjit

subashreddy
Starting Member

6 Posts

Posted - 2011-05-18 : 00:39:53

Thank you Ranjit

subash

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-18 : 06:24:29

Welcome subashreddy

--Ranjit

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:43:45