SQL converting a negative number to positive

Posted on 2005-04-12
Last Modified: 2012-08-13

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.
Question by:rudyflyer
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Lose the IF and use a where clause:

    UPDATE Table
    SET field= (field * -1)
    Where SIGN(field) = -1
    LVL 25

    Expert Comment

    update table
    set field = field * -1
    where field < 0
    LVL 68

    Accepted Solution

    There is a built-in function, ABS(), for doing that:

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

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now