Access SQL - if/then in update query

2
votes

I have table "SPPB" with fields:

Total Status

I need to update the Status field to either 1 or -9 depending on the value in the Total field. I.e., if Total is null, then Status is -9. If Total is not null, then Status is 1.

I'm having trouble with syntax in Access...

1
sqlms-access
you need to use an IIF statement - Beth

1 Answers

4
votes

Two options. The first is a single statement:

update sppb
    set status = iif(total is null, -9, 1);

Or, two statements:

update sppb
    set status = -9
    where total is null;

update sppb
    set status = 1
    where total is not null;

In this case, the single statement version is probably better, in terms of performance.