How to use SQL conditional statements

Question

I need to delete a row based upon the value in a column while executing an update query. Here is the code:

 UPDATE tag SET tag_count = tag_count - 1 WHERE tag_id = 1
 IF tag_count < 1
 delete from tag where tag_id = 1

This query here gives me an error.

EDIT I am using inline sql with C# and sql server

Daniel Vassallo · Accepted Answer · 2010-01-25 00:54:26Z

6

In general, the best option in these cases is to wrap the UPDATE and DELETE statements within a transaction:

BEGIN TRANSACTION;

UPDATE tag SET tag_count = tag_count - 1 WHERE tag_id = 1
DELETE from tag where tag_id = 1 and tag_count < 1;

COMMIT TRANSACTION;

answered Jan 25, 2010 at 0:54

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JohnFx Over a year ago

Good answer, it is always best to avoid a procedural approach when a declarative one is available. Use conditionals and other control flow structures only as a last resort in a stored procedure.

Daniel Vassallo Over a year ago

@Luke101: Yes sure, it will work. Since you are using C#, you may want to check this short example which uses a similar transaction to yours: aspnettutorials.com/tutorials/database/…

Chase Florell · Accepted Answer · 2010-01-25 00:56:07Z

0

Hmm, did you try using Begin and End?

 UPDATE tag 
 SET tag_count = tag_count - 1 
 WHERE tag_id = 1

 IF tag_count < 1
     BEGIN
       DELETE FROM tag 
       WHERE tag_id = 1
     END

answered Jan 25, 2010 at 0:56

Chase Florell

47.6k59 gold badges192 silver badges382 bronze badges

2 Comments

Daniel Vassallo Over a year ago

In this case, you would still need to SELECT tag_count FROM tag WHERE tag_id=1 before the condition statement. However this is not necessary since you can easily specify the condition in the DELETE statement.

Luke101 Over a year ago

sql server does not recognize "tag_count" in the IF conditional as a valid column when I tried this code.

Collectives™ on Stack Overflow

How to use SQL conditional statements

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related