[Solved] Query to Change Values

[lit31686]

Hello all,

Is there an SQL command I can use in a query to change the values in a table? For example, I want to change all instances of "YES" to "1", "NO" to "2", and "N/A" to "3" in 3 different fields (ID, ID 2, ID 3) of an existing table. However, I don't want to replace the existing table.

Any help is greatly appreciated.

[Sliderule]

You said / asked:

Is there an SQL command I can use in a query to change the values in a table? For example, I want to change all instances of "YES" to "1", "NO" to "2", and "N/A" to "3" in 3 different fields (ID, ID 2, ID 3) of an existing table. However, I don't want to replace the existing table.

Please help me / us understand exactly what you want / need, by answering the following questions, so, perhaps someone here can offer for you the best answer.

1. Please use double quotes ( " ) to represent table or view or column names . . . for example . . . "ID 2"
2. Please use single quotes ( ' ) to represent text strings . . . for example . . . 'YES'
3. What is the name of your table?
4. What is column type of the columns you want to change, for example, is, "ID" a text column, a boolean, an integer, something else?
5. You said you want to "use in a query" . . . to be clear, does this mean, you do NOT what to change the data content, that is, only the display / format of the output ( SELECT ) . . . OR . . . do you want to change the data content ( UPDATE ) of your table?

[lit31686]

The name of the table is "Ingredient Free Of". The table contains three different Text columns: "ID", "ID 2", and "ID 3". These three columns can have one of three different strings: 'YES', 'NO', or 'N/A'. I would like to create a new table (without replacing the old one) where all instances of 'YES' are changed to '1', all instances of 'NO' are changed to '2' and all instances of 'N/A' are changed to '3'.

Let me know if you need more information. Thank you.

[Sliderule]

OK, thank-you for the answers. Based on how you responded, specifically:

I would like to create a new table (without replacing the old one) where all instances of 'YES' are changed to '1', all instances of 'NO' are changed to '2' and all instances of 'N/A' are changed to '3'.

I would do it in steps. In English, create a 'copy' of your original table "Ingredient Free Of"[, and, then change the data contents using three UPDATE commands, one command for each of the columns you want to change.

1. Start by making, a BACKUP copy of your database file ( *.odb ) just in case.
2. Open your file, click on the Tables icon on the left.
3. Click on your table "Ingredient Free Of" and drag it so you see the + pop-up, and, assign the NEW table name you desire. This will create the new table, with, the same data as in your original table.
4. From the Menu, choose: Tools -> SQL...
5. You will see a new windows, name of: Execute SQL Statement
6. In the Command to execute window, enter the following commands, I strongly encourage you to issue the commands, ONE AT A TIME . . . to make sure no problems with the command, that is, the syntax is correct. Also, you will have to change , the table name of "Ingredient Free Of" to the name of NEW table you copied.

7. Code: Select all

   UPDATE "Ingredient Free Of" SET "ID" = CASE "ID" WHEN 'YES' = '1' WHEN 'NO' = '2' WHEN 'N/A' = '3' ELSE '???' END;
   

8. Press the Execute button
9. You should a response back, that command was successful.

10. Code: Select all

    UPDATE "Ingredient Free Of" SET "ID 2" = CASE "ID 2" WHEN 'YES' = '1' WHEN 'NO' = '2' WHEN 'N/A' = '3' ELSE '???' END;
    

11. Press the Execute button
12. You should a response back, that command was successful.

13. Code: Select all

    UPDATE "Ingredient Free Of" SET "ID 3" = CASE "ID 3" WHEN 'YES' = '1' WHEN 'NO' = '2' WHEN 'N/A' = '3' ELSE '???' END;
    

14. Press the Execute button
15. You should a response back, that command was successful.
16. Press the Close button
17. From the Menu, choose: View -> Refresh Tables
18. Review the contents of the new table, to confirm everything is as you expect. Save the file when you exit.

Explanation: The above syntax for the UPDATE statement says, for the specified table, change the data for the field defined after the keyword, SET, and, when it is 'YES' make it '1' ,and, when it is 'NO' make it '2' ,and, when it is 'N/A' make it '3' , OTHERWISE make it '???' .

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[lit31686]

Is there a way to make it so that the new table (with the numbers) is automatically updated whenever the original table (with the text) is changed?

[Sliderule]

No. Why . . . that is . . . why do you want to duplicate data?

BUT, maybe you just need to create a VIEW, rather than new table. And, the VIEW, just like a Query . . . can . . . DISPLAY the data like you want. That is why I asked, in my FIRST post to you, if you just need it for DISPLAY purposes, or, to really change the data.

Sliderule

[lit31686]

Sorry if I didn't explain it clearly. I'm still new to this, but am trying to learn.

I have a table in a form that shows the field names and text strings from the original table. However, I also want to display pictures in a separate part of the form that summarize the values ('YES', 'NO', 'N/A') listed from each field ("ID", "ID 2", "ID 3"). The pictures are displayed based off the Minimum Value in each field and with the idea that 'YES' is the lowest value, 'NO' is the second lowest value, and 'N/A' is the highest value.

For example, the values listed in the "ID" column are 'YES', 'NO', 'NO', 'NO', 'N/A' ......based on this, the minimum value should be 1 ('YES').

For "ID 2" the values are 'NO', 'NO', 'NO', 'N/A'......based on this, the minimum value should be 2 ('NO').

If this doesn't make sense, I will try my best to explain again. Thank your for your help thus far.

[Sliderule]

Please tell me this:

In your table "Ingredient Free Of" . . . what column(s) comprise the Primary Key ? That is, which column(s) make the row UNIQUE? Please be sure to include double quotes around the column name . . . since . . . if it ( the column name ) includes a space . . . I ( and the database back-end ) will know the space is not a key word etc.

NOTE: the reason I am asking is perhaps a sub-query can be used to provide the result you want.

[lit31686]

The column that makes the row unique is "INGREDIENT NUMBER".

[Sliderule]

In a post above, you said:

For example, the values listed in the "ID" column are 'YES', 'NO', 'NO', 'NO', 'N/A' ......based on this, the minimum value should be 1 ('YES').

What I need to know to help you, what is the column name, that you want to GROUP the output for? That is, what is the column name that contains the unique value, that the "ID" column is as you listed above?

Yes, the Primary Key for the table "Ingredient Free Of" is "INGREDIENT NUMBER", however, what value or values ( column name ) do you want the output you gave in the example above for, 1 ('YES') ?

[lit31686]

"ID PIC NUMBER" should be the output column name.

[Sliderule]

Please try either ( or both ) of the following Queries, to return the 'calculated' values you desire.

1. Code: Select all

   Select 
      "Ingredient Free Of"."ID PIC NUMBER",
      MIN(CASE WHEN "Ingredient Free Of"."ID" = 'YES' THEN '1' WHEN "Ingredient Free Of"."ID" = 'NO' THEN '2' WHEN "Ingredient Free Of"."ID" = 'N/A' THEN '3' ELSE '???' END) as "NEW_ID", 
      MIN(CASE WHEN "Ingredient Free Of"."ID 2" = 'YES' THEN '1' WHEN "Ingredient Free Of"."ID 2" = 'NO' THEN '2' WHEN "Ingredient Free Of"."ID 2" = 'N/A' THEN '3' ELSE '???' END) as "NEW_ID_2", 
      MIN(CASE WHEN "Ingredient Free Of"."ID 3" = 'YES' THEN '1' WHEN "Ingredient Free Of"."ID 3" = 'NO' THEN '2' WHEN "Ingredient Free Of"."ID 3" = 'N/A' THEN '3' ELSE '???' END) as "NEW_ID_3" 
   From "Ingredient Free Of" 
   Group By "Ingredient Free Of"."ID PIC NUMBER"

2. Code: Select all

   Select 
      "Ingredient Free Of"."ID PIC NUMBER", 
      MAX("Ingredient Free Of"."ID") as "NEW_ID", 
      MAX("Ingredient Free Of"."ID 2") as "NEW_ID_2", 
      MAX("Ingredient Free Of"."ID 3" as "NEW_ID_3" 
   From "Ingredient Free Of" 
   Group By "Ingredient Free Of"."ID PIC NUMBER"

Explanation: I assigned, using an Alias, distinct column names to the return set. For example, "NEW_ID_2" . You may change these to your preference if you desire. Furthermore, if you desire, you can add a Where clause, after the From line, for example:

Code: Select all

Where "Ingredient Free Of"."ID PIC NUMBER" = :Enter_ID_PIC_NUMBER

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[lit31686]

You are my savior! Thanks for your help!