Easy SQL Question 2

[ArizonaRedneck]

I'm sure it's very simple to do, but I can't figure out how to increase a value using an update statement.

I have a column named "PreviousBalance" if a condition is met I want to increase it by a value contained in the "Fee" column.
basically: <PreviousBalance> = <PreviousBalance> + <Fee>
then I reset the "Rebill" column

here's the update statement I'm trying

UPDATE    tblBilling
SET              PreviousBalance = ? + ?, Rebill = '0'
WHERE     (Rebill = '1')

First Parameter Value = "PreviousBalance"
Second Parameter Value= "Fee"

when I try to run it, it gives me the error of

Application uses a value of the wrong type for the current operation

both PreviousBalance and Fee are of type float. Rebill is a bit.

thanks in advance.

-
"Do your duty in all things. You cannot do more, you should never wish to do less." Robert E. Lee

 

[mwolf00]

UPDATE tblBilling SET previousBalance =
CASE when rebill = 1 THEN previousBalance + Fee
ELSE 0


I'm not sure if this is what you're getting at or not. If rebill is a bit type, you need to remove the quotes around it

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[SQLSister]

UPDATE    tblBilling
SET              PreviousBalance = PreviousBalance + Fee, Rebill = '0'
WHERE     (Rebill = '1')

Doesn't this work?

Questions about posting. See faq183-874

 

[ArizonaRedneck]

one more question. If PreviousBalance is null it stays null. is there an easy way to set PreviousBalance = Fee if PreviousBalance is null and add Fee if PreviousBalance is not null. I would like to keep the same where clause.

-
"Do your duty in all things. You cannot do more, you should never wish to do less." Robert E. Lee

 

[checkai]

UPDATE tblBilling
SET    PreviousBalance = PreviousBalance + Fee, 
       Rebill = 0
WHERE  Rebill = 1

this seems that it should work correctly...

 

[checkai]

UPDATE tblBilling
SET    PreviousBalance = isnull(PreviousBalance,0) + Fee, 
       Rebill = 0
WHERE  Rebill = 1

 

[SQLSister]

UPDATE    tblBilling
SET              PreviousBalance = isnull(PreviousBalance,0) + Fee, Rebill = '0'
WHERE     (Rebill = '1')

Questions about posting. See faq183-874

 

[ArizonaRedneck]

That worked perfectly. Thank you both and have a great new year!

-
"Do your duty in all things. You cannot do more, you should never wish to do less." Robert E. Lee