Improving the following update

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 2005 Forums

Transact-SQL (2005)

Improving the following update

Author

Topic

kay_hll
Starting Member

2 Posts

Posted - 2012-01-28 : 20:22:02

Hi,

I am new to this forum. I wonder if anybody can suggest a better way of doing the following:

UPDATE TermReinstate set tere_Deleted=1
FROM termreinstate
Where Tere_Deleted is NULL and Tere_TermReinstateId NOT IN
(
select MAX(Tere_TermReinstateId)
from TermReinstate
GROUP BY tere_leveltype,tere_contractId, tere_divisionId, tere_classId,tere_memberId, tere_termreinstatedt
, tere_termreasoncode, tere_Action)

Thank you,
~Kay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-28 : 21:16:01

try this and see


UPDATE t
set t.tere_Deleted=1
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY tere_leveltype,tere_contractId, tere_divisionId, tere_classId,tere_memberId, tere_termreinstatedt, tere_termreasoncode, tere_Action ORDER BY Tere_TermReinstateId DESC) AS Rn,Tere_Deleted
FROM termreinstate )t
Where Tere_Deleted is NULL 
and Rn > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kay_hll
Starting Member

2 Posts

Posted - 2012-02-02 : 02:21:37

Thanks for the suggestion. I tried it and did not help much.
Thanks anyway.

Kay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 11:48:41

quote:
Originally posted by kay_hll

Thanks for the suggestion. I tried it and did not help much.
Thanks anyway.

Kay

hmm why so?
can you explain?
didnt it work?
or is it that it worked but still took long time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources