CODEWITHSUNDEEP
sqlms-access
Hunderfert
Hunderfert

Reputation: 41

Change values after select query

Hello everyone I am having trouble and I can't seem to find an answer that will work for my problem.

I am using Microsoft Access and running a Select query. What I want to then do is change the values in a column for this query.

for example

SELECT Table.column1, Table.Column2 
FROM Table

I get the following:

|Column1|Column2|
    A       1
    B       2
    C       3
    D       2
    E       1
    F       2
    G       3
    H       3
    I       1
    J       2
    K       2

I want it so that the query now replaces all the 1s in column2 with 100, all the 2s in column2 with 200 and all the 3s in column2 with 300.

What I dont want to do is update the values in column2 in the original table, I want to change the values just in the query.

Upvotes: 1

Views: 2307

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Use the switch() function:

select column1, 
       switch(column2 = 1, 100,
              column2 = 2, 200,
              column2 = 3, 300,
              column2
             ) as new_column2
from t;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Other option with IIF() :

select Col1, iif ( Col2 = 1, 100, 
                   iif ( Col2 = 2, 200, 
                         iif ( Col2 = 3, 300,  col2 ) 
                       ) 
                 ) as col2
from table t;

Upvotes: 0

Erik A
Erik A

Reputation: 32642

You can do calculations in your select queries, e.g.:

SELECT Table.column1, Table.Column2 * 100 As Column2
FROM Table

This displays the value of Column2 multiplied by 100, but doesn't modify the underlying table.

You can't modify specific rows just in a query. If you want to modify specific rows, you can just copy the table, or use a make-table query to insert the results of a query in a table and then modify that table.

Upvotes: 1

Kobi
Kobi

Reputation: 2524

If your column2 data are only 1, 2 and 3 you can multiply by 100

SELECT Table.column1
     , Table.Column2 * 100 as Column2 
FROM Table

If your data contains other values than 1,2 and 3 you can use IIF

SELECT Table.column1
     , IIF(Table.Column2 in (1,2,3),Table.Column2 * 100, Table.Column2) as Column2 
FROM Table

Upvotes: 2

Related Questions