CODEWITHSUNDEEP
sqlms-access
Chris Ray
Chris Ray

Reputation: 51

Access SQL - if/then in update query

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...

Upvotes: 2

Views: 13291

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 4

Related Questions