Posted by Greg D. Moore \(Strider\) on 07/26/06 11:57

"NiTiN" <emailme.nitin@gmail.com> wrote in message
news:1153889484.304543.304580@h48g2000cwc.googlegroups.com...
> > i hava eetable that is ,
>
> 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")

Note there is absolutely NO guarantee this will work correctly!!!!

You are assuming that the rows will be returned in a particular order which
w/o a ORDER BY statement is not guaranteed.

Which means that your example will delete rows 2,3,4 of the dataset, but you
can't guarantee what rows those will be.




>
> 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]