Delete sql record with if statement

Question

update dbo.tblMessages
set messageFlags = (messageFlags + 1)
where messageId = @messageId

So that's the update in my proc. I want to be able to delete the record AFTER the update, IF messageFlags becomes '10' after it updates.

How would I do that?

Answer 1

Add the < 10 condition to the WHERE of the UPDATE. If you want to delete when 10 then you are saying "is it 9 now?"

-- DECLARE @rc int
update dbo.tblMessages
set
    messageFlags = (messageFlags + 1)
where messageId = @messageId and messageId < 10
/*
or assign SET @rc = @@ROWCOUNT to do more stuff first
*/
IF @@ROWCOUNT = 0
    DELETE dbo.tblMessages WHERE messageId = @messageId 

Or use the assign feature of UPDATE. Similar to the OUTPUT clause

DECLARE @newvalue int

BEGIN TRANSACTIOn

update dbo.tblMessages
set
    @newvalue = messageFlags = (messageFlags + 1)
where messageId = @messageId

IF @newvalue = 10
    DELETE dbo.tblMessages WHERE messageId = @messageId

COMMIT TRAN

It comes down to the question: do you need the value to actually be 10 first before deleting?