CODEWITHSUNDEEP
sqlms-access
Mimi
Mimi

Reputation: 97

Change value based on column

I have a simple SQL query like this that I made with MS Access:

SELECT DISTINCT MID(table1.OZ, 1, 8) as OZ, table1.column1, table1.Description, table1.PSet
FROM table1, table2
WHERE table2.OZ = table1.OZ AND 
table1.column1= table2.column1

The table looks something like this:

OZ column1 Description Pset
01.02.03 some text description pset_LG
01.02.03 some text description pset_Text
01.02.03 some text description pset_ULG

The OZ consists of numbers like this: xx.xx.xx and Pset always has three possible values: pset_LG, pset_Text and pset_ULG. Now to my question:

I want to change OZ based on Pset. If I have pset_LG, I'd like to cut off the last four numbers, so that OZ will show only 01. And if I have pset_Text, I'd like to extract the third and fourth number (02) and for pset_ULG I'd want 03, so the last two numbers. Is it possible to change my initial SQL query, so that the table gets adjusted? I tried making a new query in MS Access with UPDATE [table], but I get an error saying that the operation must use an updateable query. I generally can't really seem to get my head around my problem so I'd really appreciate all kinds of help.

Upvotes: 0

Views: 85

Answers (2)

Benoit Drogou
Benoit Drogou

Reputation: 969


SELECT
   DISTINCT MID(table1.OZ, 1, 8) as OZ,
   table1.column1,
   table1.Description,
   table1.PSet,
   CASE
      WHEN PSet = 'pset_LG' THEN MID(OZ, 1, 2)
      WHEN PSet = 'pset_Text' THEN MID(OZ, 4, 2)
      ELSE MID(OZ, 7, 2)
   END as short_OZ

FROM table1, table2
WHERE table2.OZ = table1.OZ AND 
table1.column1= table2.column1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can use conditional logic:

SELECT DISTINCT MID(table1.OZ, 1, 8) as OZ,
       table1.column1, table1.Description,
       table1.PSet,
       IIF(table1.PSet LIKE "*LG", MID(table1.OZ, 1, 2),
           IIF(table1.PSet LIKE "*Text", MID(table1.OZ, 4, 2),
               IIF(table1.PSet LIKE "*ULG", MID(table1.OZ, 7, 2), table1.OZ
                  )
              )
           ) as new_OZ
               
FROM table1 INNER JOIN
     table2
     ON table2.OZ = table1.OZ AND table1.column1 = table2.column1

Upvotes: 1

Related Questions