@@Rowcount in SQL

Question

Alternative of top class for delete and update query in sql 2000 is @@Rowcount & in Sql 2005 or 2008 we have

delete top(1) from emp where salary = 13000

update top(1) emp set salary = 1000 where salary = 13000

Above two queries can be executed only in Sql 2005 or 2008 but if we need to execute it in sql 2000 we do it with the help of @@rowcount .

Please tell me the above two query using @@rowcount.

Rushal Arora · Answer

I'll really appreciate if u'll tell me using @@rowcount. I dont need any Alternative solution.

Jignesh Trivedi · Answer

hi you can also use sub query or join query instead of @@RowCount. update table1 set field1=1 where id in (select top 1 id from table1 where field1=0) OR UPDATE table1 SET Filed1 = 1 FROM (SELECT TOP 1 FROM table1 where Id = 0) AS t1 WHERE table1.id = t1.id generally @@rowcount Returns the number of rows affected by the last statement. hope this will help you.

Rushal Arora · Answer

Hi Jijnesh Thanks 4 replying but would like to tell u that you can only use 'top' class with select in sql server 2000 not with update & delete directly and in the link u suggested top class is used with select & hence used indirectly with update . Aneways apart from this please tell me how to use @@rowcount for the update & delete query for the below (Dont use top class): delete top(1) from emp where salary = 13000 update top(1) emp set salary = 1000 where salary = 13000

Rushal Arora · Answer

one can only use top class with select in sql server 2000 not with update & delete

Jignesh Trivedi · Answer

hi As per my knowledge you can also use top key word with update and delete statement in SQL server 2000. Please refer http://msdn.microsoft.com/en-us/library/aa260662%28v=sql.80%29.aspx hope this will help you.

brunda k · Answer

http://stackoverflow.com/questions/6565399/sql-server-2000-getting-top-n-rows constraint of environment

Rushal Arora · Answer

Really Appreciable for your great efforts but i would like to tell u that no one can use top class for delete and update query in sql 2000. So Please execute your solution once in sql server 2000 & then let me know please.

brunda k · Answer

begin declare @myrowcount int delete top(1) from emp where salary = 13000 SET @myrowcount=@@rowcount if @myrowcount>0 Begin update top(1) emp set salary = 1000 where salary = 13000 End End