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.

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

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