CODEWITHSUNDEEP
sqlms-access
BLkrn
BLkrn

Reputation: 77

Simple update query in access

UPDATE _DATA SET 
[_DATA].Claim_Status = "UNKNOWN", 
[_DATA].State_Filed = "XX", 
[_DATA].Disease_Category = "UNKNOWN"
WHERE 
((([_DATA].Claim_Status) Is Null) AND 
(([_DATA].State_Filed) Is Null) AND 
(([_DATA].Disease_Category) Is Null));

This code is what I got from typing in criteria in query design. It seems that this only runs when claim_status, state_filed, and disease_category are all null (empty) cells. I want to run a query that fills in "UNKNOWN" into empty cells under Claim_Status, "XX" int empty cells under State_Filed, etc. The condition should not include "AND" which means they are basically run separately.

Can anyone help me with this?

Thanks in advance.

Upvotes: 0

Views: 355

Answers (2)

Gustav
Gustav

Reputation: 55806

You can use Nz:

UPDATE 
    _DATA 
SET 
    Claim_Status = Nz(Claim_Status, "UNKNOWN"), 
    State_Filed = Nz(State_Filed, "XX"), 
    Disease_Category = Nz(Disease_Category, "UNKNOWN")
WHERE 
    Claim_Status Is Null 
    OR 
    State_Filed Is Null 
    OR 
    Disease_Category Is Null;

Upvotes: 1

Patrick Tucci
Patrick Tucci

Reputation: 1952

This query will only update Claim_Status, State_Filed and Disease_Category if all three of those fields are NULL. Say, for instance, that the State_Filed is NULL, but Disease_Category and Claim_Status have data populated. In this case, your update would not update State_Filed because not all three columns are NULL. You should use three separate queries to make sure that all NULL columns get updated regardless of if the other two are populated:

UPDATE _DATA
SET Claim_Status = 'UNKNOWN' 
WHERE Claim_Status IS NULL

UPDATE _DATA
SET State_Filed = 'XX' 
WHERE State_Filed IS NULL

UPDATE _DATA
SET Disease_Category = 'UNKNOWN' 
WHERE Disease_Category IS NULL

Also, I would personally not update NULL fields to contain data. If you don't have data for these columns, you don't have the data. If, for instance, you want a NULL state to show as XX instead of NULL, the place to do this is in your presentation layer or reports that you write off this data, not the database itself.

Upvotes: 1

Related Questions