Delete alternate rows - 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)

Delete alternate rows

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-19 : 03:02:18

Hi, I have a table containing 10 rows and I have to delete alternate rows in the table

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 03:11:07

Somthing like this


Declare @var Table 
(
col1 int identity(1,1),
col2 int
)

Insert @var
Select 1 Union All 
Select 2 Union All 
Select 3 Union All 
Select 4 Union All 
Select 5 Union All 
Select 1 Union All 
Select 2 Union All 
Select 3 Union All 
Select 4 Union All 
Select 5 


delete from @var Where Col1 % 2 =0

Select * from @var

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-19 : 03:37:27

Is it possible to do with a single query statement

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 03:48:14

its the single query only, i just showed by giving one sample. post the table strucutre with some sample data so it can be more clear

Delete query is this much only.
delete from @var Where Col1 % 2 =0

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-19 : 03:54:43

I agree with you. But I wanted to do the same with a single SQL query.I am not supposed to do any other operation. I have to use only one delete statement

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 04:18:09

quote:
Originally posted by vmurali

I agree with you. But I wanted to do the same with a single SQL query.I am not supposed to do any other operation. I have to use only one delete statement

Yeah, this can be done only with one delete statement only, but for that you to give some more information, like table structure and same sample data, so we can have better understanding about it and help you out oks?

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-19 : 04:46:00

candid canfname candlname
1 sri kumaran
2 sri kumaran
3 val kumar
4 shyam raman
5 balaji k
6 jack straw
7 Jeel shetty
8 ram mohan
9 raja rangan
10 ram shiva

delete rows 1, 3, 5, 7, 9

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 04:52:25

Delete From <TableName> Where CandId %2 <> 2

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-19 : 05:22:59

should be <> 0 not 2

delete table where canid % 2 <> 0

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 05:25:34

opps typo

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-19 : 05:34:52

quote:
Originally posted by chiragkhabaria

opps typo

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 00:44:14

quote:
Originally posted by vmurali

I agree with you. But I wanted to do the same with a single SQL query.I am not supposed to do any other operation. I have to use only one delete statement

Didnt you see the previous example correctly?
Also alternate row has no meaning until you have unique key in the table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources