Logical Tests in UPDATE...SET queries

Question

I need to add some columns to a table whose values depend on existing columns. Using UPDATE...SET (I assumed). I can't get it to work. Simple reprex:

CREATE TABLE my_table
(
    cost decimal(10,2),
    ref int
);

INSERT INTO my_table(cost, ref)
VALUES      (0.00, 1),
            (0.50, 1),
            (-1.89, 1),
            (0.00, 2),
            (0.00, 2),
            (0.00, 2),
            (1.23, 3),
            (-9.47, 3),
            (111.23, 3),
            (12.00, 3);
    
-- want a field that denotes positive or negative cost values
    
ALTER TABLE my_table
ADD         cost_sign bit;
    
UPDATE my_table
SET    cost_sign = (cost > 0);

This gives me an error: Incorrect syntax near '>'.

Can someone help me fix this please?

Answer 1

The boolean expression cost > 0 is not behaving as expected. Try using a CASE expression as the RHS of the assignment:

UPDATE my_table
SET cost_sign = CASE WHEN cost > 0 THEN 1 ELSE 0 END;

The comment by @Damien is valid and you may not even want to maintain this computed column as it is storing derived data. Should the cost data change, you could be forced to run this update again. Instead, look into using a generated column.

Answer 2

What you need here is a computed column; you can use the sign function

ALTER TABLE my_table
ADD Cost_Sign as sign(cost);