CODEWITHSUNDEEP
sqlms-access
tee
tee

Reputation: 1336

SQL updating table with multiple conditions in the where statement ACCESS

I'm trying to update multiple columns in a table. Here is an example of what I'm trying to do:

Say I have this table:

User | Letter1 | Letter2 
 1   | 'A'     |  'X'
 2   | 'X'     |  'X'
 3   | 'R'     |  'G'

And i want to convert it to a table like this where all the X's are replaced by "_" (underscores).

User | Letter1 | Letter2 
 1   | 'A'     |  '_'
 2   | '_'     |  '_'
 3   | 'R'     |  'G'

I tried this:

Update table1 
SET Letter1 = '_', Letter2 = '_'
WHERE Letter1 = "X", Letter2 = "X"

However that doesn't work. Any help would be great. Thanks in advance

Upvotes: 1

Views: 11735

Answers (3)

user23201197
user23201197

Reputation: 1

I've used the SWITCH statement in MS Access for executing a multiple condition update on a single field. This example sets the value in the field myt_Field1 depending on the value in myt_Field2:

UPDATE tbl_MyTable 
  SET tbl_MyTable.myt_Field1 = 
  SWITCH(
    LEFT(myt_Field2, 3) = "AD2", "D",
    LEFT(myt_Field2, 3) = "AB1", "B",
    LEFT(myt_Field2, 3) = "AAB", "A",
    LEFT(myt_Field2, 3) = "AFS", "F",
    LEFT(myt_Field2, 3) = "AFW", "F",
    LEFT(myt_Field2, 3) = "AG0", "G",
    LEFT(myt_Field2, 3) = "AMT", "M",
    LEFT(myt_Field2, 3) = "APR", "P",
    LEFT(myt_Field2, 3) = "ASU", "S",
    LEFT(myt_Field2, 3) = "ATE", "T");

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want to do this in a single update, you'll need conditional logic:

Update table1 
    SET Letter1 = IIF(Letter1 = "X", "_", Letter1),
        Letter2 = IIF(Letter1 = "X", "_", Letter2)
    WHERE Letter1 = "X" OR Letter2 = "X";

However, two separate statements might be clearer:

Update table1 
    SET Letter1 = "_"
    WHERE Letter1 = "X";

Update table1 
    SET Letter2 = "_"
    WHERE Letter2 = "X";

Upvotes: 4

Jim Macaulay
Jim Macaulay

Reputation: 5141

Use below query

Update table1 
SET Letter1 = '_', Letter2 = '_'
WHERE Letter1 = 'X' or Letter2 =  'X';

Or use two update statements

Update table1 
SET Letter1 = '_'
WHERE Letter1 = 'X';

Update table1 
SET  Letter2 = '_'
WHERE  Letter2 =  'X';

Upvotes: 1

Related Questions