Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 21283
  • Last Modified:

SQL converting a negative number to positive

Hello,

In our SQL Server database we have money values that are negative.  But I need to convert all negative values to positive values.  I am using the following code in Query Analyzer:

--Convert all negative numbers to positive numbers
IF (SELECT field FROM table) < 0
      UPDATE Table
      SET field= (field * -1)

But I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What am I doing wrong?  Or is there a better way to get the absolute value of a negative number?

Thanks in advance.
0
rudyflyer
Asked:
rudyflyer
1 Solution
 
Anthony PerkinsCommented:
Lose the IF and use a where clause:

UPDATE Table
SET field= (field * -1)
Where SIGN(field) = -1
0
 
jrb1Commented:
update table
set field = field * -1
where field < 0
0
 
Scott PletcherSenior DBACommented:
There is a built-in function, ABS(), for doing that:

UPDATE table
SET column = ABS(column)
WHERE column < 0
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now