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 开发者_JAVA百科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)

---

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.

---

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.

---

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)