CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2013
Macs
Macs

Reputation: 61

Access Update Query Unique Criteria in multiple column

I have a table like this (Access Version 2013)

Block   SAP SAG BAP BAG 
600400  1   3        
600401          1   3 
600402              
600403  1   3   2   4 
600404          1   3 
600405          3   4 
600406  1   4        
600407  1   2   1   3
600408  4   5        
600409  1   1   3   1
600410  1   5   1   3

I want to update the table like this below, basically I want to null the (SAP & SAG = NULL) if (SAP = 1 AND SAG = 3), this one i can achieve easily, problem is I want to do the same for (BAP & BAG = NULL) if (BAP = 1 AND BAG = 3) without affecting SAP and SAG, i tried various ways, but end up with unusual result

Block   SAP SAG BAP BAG 
600400            
600401              
600402              
600403          2   4 
600404              
600405          3   4 
600406  1   4        
600407  1   2      
600408  4   5        
600409  1   1   3   1
600410  1   5

Is it possible to achieve with a single query (or) I need to use two different update query ? Any Suggestions ?

Upvotes: 1

Views: 387

Answers (2)

donCalculator
donCalculator

Reputation: 318

If you dont need to modify the first table, then its much simpler to NOT select the items you dont want, using access functions.

SELECT Iif(Sap<>"1" AND Sag<>"3"; Sap, "") AS SAP, Iif(Sap<>"1" AND Sag<>"3"; Sag, "") AS SAG, Iif(Bap<>"1" AND Bag<>"3"; Bap, "") AS BAP, Iif(Bap<>"1" AND Bag<>"3"; Bag, "") AS BAG FROM Table;

Upvotes: 0

Andre
Andre

Reputation: 27634

You need two queries, because they affect different (but overlapping) sets of rows.

It's also simple and readable. Why would you not want to use two queries?

UPDATE foo 
SET SAP = NULL, SAG = NULL
WHERE SAP = 1 AND SAG = 3;

UPDATE foo 
SET BAP = NULL, BAG = NULL
WHERE BAP = 1 AND BAG = 3;

Upvotes: 1

Related Questions