Home / Questions / 282880
Accepted
Huntzberger
Asked: 2020-08-01 09:27:47 +0800 CST 2020-08-01 09:27:47 +0800 CST

Help with Update SQL Server

  • 772
Codigo varchar
Cantidad float
Cantidad2 float
Cantidad3 float

I have a table in SQL Server which gives me the following result

Codigo Cantidad Cantidad2 Cantidad3
00428     0         0       32

Now I need to add 1 to the float columns but that they are different from 0, this is my query but even if they have a value of 0 if they add the number

update SIPGProyeccion set Cantidad=(Cantidad+1),Cantidad= 
                          (Cantidad2+1),Cantidad3=(Cantidad3+1)
where Codigo= '00428' 
and Cantidad<>0 
and Cantidad2 <>0 
and Cantidad3<>0
sql-server

2 Answers

  1. update SIPGProyeccion 
       set Cantidad  = Cantidad  + (case when Cantidad <> 0  then 1 else 0 end),
           Cantidad1 = Cantidad1 + (case when Cantidad1 <> 0 then 1 else 0 end),
           Cantidad2 = Cantidad2 + (case when Cantidad2 <> 0 then 1 else 0 end)
       where Codigo= '00428' 
             and (Cantidad<>0 or Cantidad2 <>0 or Cantidad3<>0)

    Details:

    • First of all you have to modify the where, it must be valid for anything Cantidadthat is different from 0
    • The next thing is to use a caseto determine if we will add 1 or 0 based on the condition of each Cantidad.
    • 2
  2. Best Answer

    If you want to add 1 only to the columns that do not have 0, you can do it with the following query:

    update SIPGProyeccion set Cantidad=(Cantidad+iif(cantidad=0,0,1)),Cantidad2=(Cantidad2+iif(cantidad2=0,0,1)),Cantidad3=(Cantidad3+iif(cantidad3=0,0,1)) where Codigo= '00428'

    As you will see, the use of IIF allows us to add 0 or 1 according to the current value of the corresponding column.

    In the where, the condition that you had to check that all the values ​​were 0 was eliminated, since this would cause that only if the three columns were 0, they would be increased by 1.

    • 1