I have a column which I need to set to true or false based on a condition, is this possible to do as part of an existing update?
Example:
UPDATE i
SET i.Outstanding = i.Total - @Payments,
i.Paid = @Payments ,
i.Closed = (i.Total <= @Payments) -- THIS DOESNT WORK :(
FROM Invoice i
JOIN [Transaction] t ON t.Invoice_Id = i.Id
WHERE i.Id = @InvoiceId
You can use CASE statement
UPDATE i
SET i.Outstanding = i.Total - @Payments,
i.Paid = @Payments ,
i.Closed = CASE WHEN i.Total <= @Payments THEN 1 ELSE 0 END
FROM Invoice i
JOIN [Transaction] t ON t.Invoice_Id = i.Id
WHERE i.Id = @InvoiceId
You can use simple case statement like below
UPDATE i
SET i.Outstanding = i.Total - @Payments,
i.Paid = @Payments ,
i.Closed = CASE WHEN i.Total <= @Payments THEN 'Your value' ELSE 'your value' END
FROM Invoice i
JOIN [Transaction] t ON t.Invoice_Id = i.Id
WHERE i.Id = @InvoiceId
Try this:
UPDATE i
SET i.Outstanding = i.Total - @Payments,
i.Paid = @Payments ,
i.Closed = CASE WHEN i.Total <= @Payments THEN 'true' ELSE 'false' END
FROM Invoice i
JOIN [Transaction] t ON t.Invoice_Id = i.Id
WHERE i.Id = @InvoiceId
CASE Syntax:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Read more about CASE here.
