[Solved] Query to Change Values

Creating tables and queries

[Solved] Query to Change Values

Postby lit31686 » Mon Jan 11, 2016 7:10 pm

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.
Last edited by RoryOF on Tue Jan 12, 2016 7:51 pm, edited 2 times in total.
Reason: Added [Solved] [RoryOF, Moderator]
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Query to Change Values

Postby Sliderule » Mon Jan 11, 2016 8:03 pm

You said / asked:
lit31686 wrote: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?
User avatar
Sliderule
Volunteer
 
Posts: 1243
Joined: Thu Nov 29, 2007 9:46 am

Re: Query to Change Values

Postby lit31686 » Mon Jan 11, 2016 9:05 pm

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.
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Query to Change Values

Postby Sliderule » Mon Jan 11, 2016 9:46 pm

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

lit31686 wrote: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. :super:
  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   Expand viewCollapse view
    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   Expand viewCollapse view
    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   Expand viewCollapse view
    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.
User avatar
Sliderule
Volunteer
 
Posts: 1243
Joined: Thu Nov 29, 2007 9:46 am

Re: Query to Change Values

Postby lit31686 » Mon Jan 11, 2016 10:34 pm

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?
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Query to Change Values

Postby Sliderule » Mon Jan 11, 2016 10:40 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1243
Joined: Thu Nov 29, 2007 9:46 am

Re: Query to Change Values

Postby lit31686 » Tue Jan 12, 2016 12:06 am

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.
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Query to Change Values

Postby Sliderule » Tue Jan 12, 2016 12:26 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1243
Joined: Thu Nov 29, 2007 9:46 am

Re: Query to Change Values

Postby lit31686 » Tue Jan 12, 2016 4:14 pm

The column that makes the row unique is "INGREDIENT NUMBER".
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Query to Change Values

Postby Sliderule » Tue Jan 12, 2016 6:02 pm

In a post above, you said:

lit31686 wrote: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') ?
User avatar
Sliderule
Volunteer
 
Posts: 1243
Joined: Thu Nov 29, 2007 9:46 am

Re: Query to Change Values

Postby lit31686 » Tue Jan 12, 2016 6:21 pm

"ID PIC NUMBER" should be the output column name.
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Query to Change Values

Postby Sliderule » Tue Jan 12, 2016 6:51 pm

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

  1. Code: Select all   Expand viewCollapse view
    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   Expand viewCollapse view
    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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1243
Joined: Thu Nov 29, 2007 9:46 am

Re: Query to Change Values

Postby lit31686 » Tue Jan 12, 2016 7:37 pm

You are my savior! Thanks for your help!
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests