CREATE PROCEDURE dbo.update_IM_history_deleted
(
@userID int
)
AS SET NOCOUNT ON


UPDATE tblInstantMessage SET deletedByRecipient = Case When 
messageToID = @userID Then 1 Else deletedByRecipient  End , deletedBySender = Case When  
messageFromID = @userID Then 1 Else deletedBySender End 

quote:

---

I've read that "update TOP ....." is new functionality in SQL2005, which I plan to upgrade to soon but I am looking for a way to do this in SQL2000 currently. Is there any decent way to do this??

---

Set Rowcount 2000

UPDATE tblInstantMessage 
SET deletedByRecipient = 1 
WHERE messageToID = @userID

Set Rowcount 0

quote:

---

UPDATE authors
SET state = 'ZZ' 
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id

---

quote:

---

Originally posted by Peso

Add a
WHERE @UserID IN (messageToID, messageFromID)

to optimize the solution and restrict the number of valid records to update.


Peter Larsson
Helsingborg, Sweden

---

UPDATE		tblInstantMessage
SET		deletedByRecipient = 	Case
						When messageToID = @userID Then 1
						Else deletedByRecipient
					End,
		deletedBySender =	Case
						When messageFromID = @userID Then 1
						Else deletedBySender
					End 
WHERE		@UserID IN (messageToID, messageFromID)

UPDATE		t 
SET		deletedByRecipient = 	Case
						When messageToID = @userID Then 1
						Else deletedByRecipient
					End,
		deletedBySender =	Case
						When messageFromID = @userID Then 1
						Else deletedBySender
					End 
From 
(
	Select ROW_NUMBER()
	OVER (ORDER BY MessageFromID,deletedBySender,deletedByRecipient) AS 'RowNumber',* 
	From update_IM_history_deleted Where @UserID IN (messageToID, messageFromID)
) As t 
WHERE		
RowNumber Between 1 to 2000 

You can change the rownumber then from 2000 - 4000 accordingly.. 

and for order by clause in the select make sure that you entry a primary to that column and remove other queries. 

UPDATE    tblInstantMessage
SET       deletedByRecipient = 1
WHERE     messageToID = @userID
          AND deletedByRecipient <> 1

UPDATE    tblInstantMessage
SET       deletedBySender = 1
WHERE     messageFromID = @userID
          AND deletedBySender <> 1

UPDATE    tblInstantMessage
SET       deletedByRecipient = Case
                                  When messageToID = @userID Then 1
                                  Else deletedByRecipient
                               End,
          deletedBySender = Case
                              When messageFromID = @userID Then 1
                              Else deletedBySender
                            End 
WHERE     @userID IN (messageToID, messageFromID)

UPDATE    tblInstantMessage
SET       deletedByRecipient = 1
WHERE     messageToID = @userID
          AND deletedByRecipient <> 1
          AND InstantMessageID > 0

UPDATE    tblInstantMessage
SET       deletedBySender = 1
WHERE     messageFromID = @userID
          AND deletedBySender <> 1
          AND InstantMessageID > 0