CODEWITHSUNDEEP
t-sqlconditional-statements
scarhand
scarhand

Reputation: 4337

adding conditional statement inside UPDATE

The query:

UPDATE empPac
    SET quantityLimit = allocation,
        allocationStart = '"&allocationStart&"',
        nextUpdate = DATEADD(mm, allocationMonths, "&allocationStart&"),
        lastUpdate = GETDATE(),
        quantityIssued = 0,
        quantityShipped = 0
    WHERE allocation IS NOT NULL AND
          allocationMonths <> 0 AND
          (nextUpdate <= DATEADD(mm, "&checkCondition&", GETDATE()) OR
           nextUpdate IS NULL) AND
          empIdent in (select empIdent
                       from employee
                       where custIdent='"&custIdent&"')

What I want to do is add a conditional statement to the SET quantityLimit = allocation so that rather than having the WHERE allocation IS NOT NULL, I want it to have a conditional statement such as SET quantityLimit = ((allocation IS NULL) ? 0 : allocation)

Upvotes: 2

Views: 164

Answers (3)

NullUserException
NullUserException

Reputation: 85458

You can use ISNULL():

SET quantityLimit = ISNULL(allocation, 0)

Equivalent functions for other databases are NVL() for Oracle and IFNULL() for MySQL and SQLite

What you really should be using though is COALESCE() if you want to increase the portability of your code. COALESCE is part of the SQL-92 standard and widely supported across RDBMSes.

Upvotes: 2

Andrei LED
Andrei LED

Reputation: 2699

What database do you use? For example, in oracle sql you can write case when allocation is null then 0 else allocation end or nvl (allocation, 0) or coalesce (allocation, 0)

And case syntax in MSSQL is the same as in Oracle.

Upvotes: 1

Chains
Chains

Reputation: 13157

This is the TSQL (MSSQL) way:

SET quantityLimit = isnull(allocation,0)

Alternatives...

SET quantityLimit = CASE WHEN allocation is null THEN 0 ELSE allocation END
--This one might be handy if you wanted to check for more than just null values.  Such as:
----...CASE WHEN allocation is null THEN 0 WHEN some_other_value THEN 1 WHEN ... THEN ... ELSE allocation END

SET quantityLimit = coalesce(allocation,0)
--This one gives you the first non-null value it finds, given a list of places to look.  Such as:
----...coalesce(allocation,some_other_field,some_nuther_field,...,0)

Upvotes: 0

Related Questions