Making values positive or negative based off another column

Transact-SQL
1

Hi

Is it possible to update a column in a table and have it changed whether the value it positive or negative based of another column and if so how would i do it.

examples

cost unrealized
5 -10

so, I would want the new data to be.

cost unrealized
-5 -10

cost unrealized
-5 10

so, I would want the new data to be.

cost unrealized
5 10

and then if the 2 columns are both positive or negative do nothing.

2

Yes, it's possible. As you didn't provide any example in sql it's hard to give you a working example but it would look like something like this:

UPDATE yourtable SET a= CASE WHEN a < 0 AND b > 0 THEN a + b
WHEN a > 0 and b < 0 then a + b ELSE a END
WHERE (a < 0 and b > 0) OR (a > 0 and b < 0);

1 Like
3

If you want the 'Cost' to be the same sign as unrealized:

UPDATE yourtable
   SET cost = sign(unrealized) * abs(cost)
 WHERE sign(cost) <> sign(unrealized)

You don't really need to filter - if both are already the same then the result of the above will be the same.

1 Like