Link to home
Create AccountLog in
Avatar of devoinr

asked on

SQL Update Statment Cannot divid by Zero

I have a update statment and I am evening filtering the zero out, but I still get this error message
"Msg 8134, level 16 state 1, line 1
"Divide by zero error encoutered"

Update dbo.TempVaultSummary
SET NumberBundels =  FRBOrders/PerBundels
FROM dbo.TempVaultSummary
WHERE FRBOrders <> 0

The field FBRORDERs and have zeros but when I use the where clause and filter them out. I still get the error message.
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

SELECT  NumberBundels = ( CASE WHEN PerBundels = 0 THEN 0
                               ELSE FRBOrders / PerBundels
                          END )
FROM    dbo.TempVaultSummary
WHERE   FRBOrders <> 0

Open in new window

Avatar of Aneesh
Flag of Canada image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If you want NumberBundels to remained unchanged when the denominator is 0, then:

Update dbo.TempVaultSummary
SET NumberBundels =  
(CASE WHEN PerBundels = 0 THEN NumberBundels
ELSE FRBOrders/PerBundels END)
FROM dbo.TempVaultSummary
Avatar of devoinr


Thanks aneeshattingal:
 this worked great, man I was close