CODEWITHSUNDEEP
sqlsql-servert-sql
UserSN
UserSN

Reputation: 1013

SQL Update if null add 1 otherwise add 1 to current value

I have a query that is updating a field in my table. It could be the case that that column to be updated can be NULL if that's the case i'd like to add 1 to that cell. Otherwise i'd like to add 1 to the current value of that field.

UPDATE SET Scheduled = Scheduled + 1

Would work except when cell's have NULL as their value, it does not add the 1 value.

Upvotes: 2

Views: 2083

Answers (4)

SevincQurbanova
SevincQurbanova

Reputation: 366

Update yourTable set yourColumn=(coalesce(yourColumn,0)+1)

Or you can use

Update yourTable set yourColumn=(nullif(yourColumn,0)+1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Although you can easily do this in the update:

update t
    set scheduled = coalesce(scheduled + 1, 1)
    where . . .;

I would suggest removing the need for it, by defaulting the value to 0. I suspect that will make sense in your context. If you have data in the table:

update t
    set scheduled = 0
    where scheduled is null;

alter table t alter scheduled int not null default 0;

(Note: You can also use with values in the alter, but the update clearly shows the intent.)

Upvotes: 3

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this.

UPDATE table SET Scheduled = ISNULL(Scheduled,0) + 1

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use CASE expression:

UPDATE table_name
SET Scheduled = CASE WHEN Scheduled IS NULL THEN 1
                     ELSE Scheduled + 1
                END
WHERE ...;

Upvotes: 3

Related Questions