SQL IF statement possible?

Question

2.50/5 (2 votes)

See more:

So I have this Query as follows:

SQL

UPDATE CustomerBankInfo
SET [Money] = ([Money] - @orderValue)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
                                     FROM Customer
                                     WHERE FirstName = @custFName
                                     AND LastName =@custLName)

However I don't want it to run if 'Money' is less than 0. I've tried doing:

SQL

UPDATE CustomerBankInfo
SET [Money] = ([Money] - @orderValue)
IF [Money] > 0
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
                                     FROM Customer
                                     WHERE FirstName = @custFName
                                     AND LastName =@custLName)

Doesn't work. Any suggestions? Thanks.

Posted 13-Dec-11 7:31am

WurmInfinity

Add a Solution

3 solutions

Solution 3

You didn't provide enough detail regarding datatype of your table attributes and variable, let me guess you are using datatype money for your [Money] field. If i am right, try using the following code:

SQL

UPDATE CustomerBankInfo
SET [Money] = ([Money] - CONVERT(money,@orderValue)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
                                     FROM Customer
                                     WHERE FirstName = @custFName 
                                     AND LastName =@custLName) 
AND ([Money] > CONVERT(money,0))

Posted 13-Dec-11 7:50am

Monjurul Habib

Comments

WurmInfinity 13-Dec-11 13:53pm

Apologies. Its misleading the field is actually an integer. But thanks for the advice I'll keep that one for another day I'm sure it'll come in handy!

Monjurul Habib 13-Dec-11 13:56pm

:)

Sergey Alexandrovich Kryukov 13-Dec-11 14:13pm

Exactly, a 5. Recommended as a final answer for OP to accept formally.
--SA

Monjurul Habib 13-Dec-11 14:16pm

thank you very much for your recommendation. I got the acceptance :) thanks again

Sergey Alexandrovich Kryukov 13-Dec-11 14:23pm

That was the recommendation to OP who should formally accept this answer. It is most comprehensive so far.
--SA

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graham Breach · Accepted Answer · 2011-12-13T07:38:00

Solution 1

Put AND Money > 0 into your where clause.

Posted 13-Dec-11 7:38am

Graham Breach

Comments

WurmInfinity 13-Dec-11 13:48pm

Cheers

Sergey Alexandrovich Kryukov 13-Dec-11 14:12pm

Exactly, my 5.
--SA

Monjurul Habib 13-Dec-11 14:17pm

5!

Sergey Alexandrovich Kryukov · Accepted Answer · 2011-12-13T07:40:00

Solution 2

The right idea is: put your condition for Money in WHERE clause.

—SA

Posted 13-Dec-11 7:40am

Sergey Alexandrovich Kryukov

Updated 13-Dec-11 9:31am

v3

Comments

WurmInfinity 13-Dec-11 13:48pm

Thanks

Sergey Alexandrovich Kryukov 13-Dec-11 14:12pm

You are welcome.
--SA

Monjurul Habib 13-Dec-11 14:17pm

5!

Sergey Alexandrovich Kryukov 13-Dec-11 14:22pm

Thank you, Monjurul. You are too nice to this rather lazy answer. :-)
--SA

Monjurul Habib 13-Dec-11 14:25pm

:)