CODEWITHSUNDEEP
t-sql
Scott
Scott

Reputation: 4210

Delete sql record with if statement

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?

Upvotes: 3

Views: 14101

Answers (3)

gbn
gbn

Reputation: 432421

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?

Upvotes: 6

mtvt
mtvt

Reputation: 1

have you considered CASE statement? http://www.tizag.com/sqlTutorial/sqlcase.php

Upvotes: -1

judda
judda

Reputation: 3972

In your proc, you could either have it check for the messageFlags value. If it is going to be 10 (i.e. before the insert happens) then delete it or if you want it after the update happens delete it then (after checking).

You could also use an update trigger to delete it when the value becomes 10.

Upvotes: 0

Related Questions