adding conditional statement inside UPDATE

Question

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)

MSSQL. the conditional statement i wrote is PHP shorthand, but i didnt know how else to show an example of what i wanted done. — scarhand, Sep 22 '11 at 18:47

NullUserException · Accepted Answer · 2011-09-22T19:13:57.650

2

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.

edited Sep 22 '11 at 19:13

answered Sep 22 '11 at 18:48

NullUserException

83,810
28
209
234

And at least in Oracle you'd better use coalesce because nvl is a pl/sql function and coalesce is natively implemented. http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce – Andrei LED Sep 22 '11 at 18:58
Isn't COALESCE the ANSI standard? – Conrad Frix Sep 22 '11 at 19:05
@ConradFrix It is. I amended my answer. – NullUserException Sep 22 '11 at 19:12

Andrei LED · Answer 2 · 2011-09-22T19:01:49.393

1

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.

edited Sep 22 '11 at 19:01

answered Sep 22 '11 at 18:46

Andrei LED

2,560
17
21

score 0 · Answer 3 · answered Sep 22 '11 at 18:56

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)

adding conditional statement inside UPDATE

3 Answers3