Posted by NiTiN on 07/26/06 04:51

> i hava eetable that is ,
> eename sal
> _______ ________
> suresh 100000

-- SNIP --

> so i want to delete from second to Fourth row by using Rownumber

-- SNIP --


Hi!

I've written up an example, but with different names. I used:
-- dbtwo as the database name (I don't know what your database name
is; you may not have to specify it, if you're using the default, so
just leave out the USE statement)
-- dbo.dummy1 as the table name (for you, this would be "eetable"
instead of "dummy1")
-- ename & sal as the column names (for you, this would be "eename"
instead of "ename")

To adapt it to your scenario, change the CURSOR FOR select statement
to:
DECLARE CUR1 CURSOR FOR SELECT eename, sal FROM eetable

Apart from that, the example achieves the objective of deleting rows 2
to 4. To improve performance, you could add a break if the @counter
exceeds 4, but I just wanted to keep it short and simple.


USE dbtwo
DECLARE CUR1 CURSOR FOR SELECT ename, sal FROM [dbo].[dummy1]
DECLARE @counter TINYINT
DECLARE @ename VARCHAR(50)
DECLARE @sal SMALLINT
OPEN CUR1
SET @counter = 1
FETCH NEXT FROM CUR1 INTO @ename, @sal
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @counter = @counter + 1
FETCH NEXT FROM CUR1 INTO @ename, @sal
IF ((@counter>1) AND (@counter<5))
BEGIN
DELETE FROM [dbo].[dummy1] WHERE ename=@ename AND sal=@sal
END
END


If you were using Oracle, you could've used ROWNUM, which is a
pseudocolumn for every result set... or you can write PL/SQL, just like
the above T-SQL example.

N.I.T.I.N.

[Back to original message]