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
– scarhand, Commented Sep 22, 2011 at 18:47

NullUserException · Accepted Answer · 2011-09-22 19:13:57Z

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, 2011 at 19:13

answered Sep 22, 2011 at 18:48

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrei LED Over a year ago

And at least in Oracle you'd better use coalesce because nvl is a pl/sql function and coalesce is natively implemented. stackoverflow.com/questions/950084/…

Conrad Frix Over a year ago

Isn't COALESCE the ANSI standard?

NullUserException Over a year ago

@ConradFrix It is. I amended my answer.

Andrei LED · Accepted Answer · 2011-09-22 19:01:49Z

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, 2011 at 19:01

answered Sep 22, 2011 at 18:46

Andrei LED

2,72920 silver badges25 bronze badges

Comments

Chains · Accepted Answer · 2011-09-22 18:56:35Z

0

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)

answered Sep 22, 2011 at 18:56

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Collectives™ on Stack Overflow

adding conditional statement inside UPDATE

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related