CODEWITHSUNDEEP
sqlsql-server
shenku
shenku

Reputation: 12430

How can I set a column based on a condition in SQL Server?

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

Upvotes: 0

Views: 69

Answers (3)

Raging Bull
Raging Bull

Reputation: 18737

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.

Upvotes: 0

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

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

Upvotes: 0

Dipendu Paul
Dipendu Paul

Reputation: 2753

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

Upvotes: 3

Related Questions