Solved Combine two identically structured update queries? (1 Viewer)

[gojets1721]

I have two update queries that update two fields in the same table to 'N/A' if the respective field is null.

Here they are:

Query 1:

UPDATE tblComplaints SET Location = "N/A"
WHERE Location Is Null;

Query 2:

UPDATE tblComplaints SET Category = "N/A"
WHERE Category Is Null;

For simplicity, it'd be nice to just combine these into one update query but idk if that's possible. I tried the below but it only updates the fields if both are null.

UPDATE tblComplaints SET Location = "N/A", Category = "N/A"
WHERE Location Is Null AND Category Is Null;

I then tried below but this one updates both even if only one of them is null.

UPDATE tblComplaints SET Location = "N/A", Category = "N/A"
WHERE Location Is Null OR Category Is Null;

Maybe it's just not possible. Curious your thoughts tho. Thanks!

 

[ebs17]

UPDATE tblComplaints SET Location = Nz(Location, "N/A"), Category = Nz(Category, "N/A")

However, the two individual queries above make more sense.

 

[plog]

What does "N/A" do for you that NULL doesn't?

How does NULL get in there? Why not disallow nulls at the table level?

 

[Pat Hartman]

I agree, do not do this. If you want to show N/A rather than a blank, do that with the format property of the control.

However, the question itself has merit because you might want to update multiple fields without running multiple queries so @ebs17 gave you the syntax. The difference between this query and the original two queries, is that the two original queries might update only a few rows out of millions in a large database but the combined version will update every single row in the table. It replaces the original value with itself or "N/A".

This type of bulk update is almost always done to correct invalid data so I would also run the queries individually with the appropriate criteria to limit their scope.

 

[gojets1721]

Gotcha! Thanks for the help everyone!