[Solved] Query to Change Values
[Solved] Query to Change Values
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.
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]
Reason: Added [Solved] [RoryOF, Moderator]
OpenOffice 4.1.1 on Windows 7
Re: Query to Change Values
You said / asked:
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.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 use double quotes ( " ) to represent table or view or column names . . . for example . . . "ID 2"
- Please use single quotes ( ' ) to represent text strings . . . for example . . . 'YES'
- What is the name of your table?
- What is column type of the columns you want to change, for example, is, "ID" a text column, a boolean, an integer, something else?
- 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?
Re: Query to Change Values
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.
Let me know if you need more information. Thank you.
OpenOffice 4.1.1 on Windows 7
Re: Query to Change Values
OK, thank-you for the answers. Based on how you responded, specifically:
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.
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.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'.
- Start by making, a BACKUP copy of your database file ( *.odb ) just in case.
- Open your file, click on the Tables icon on the left.
- 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.
- From the Menu, choose: Tools -> SQL...
- You will see a new windows, name of: Execute SQL Statement
- 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.
Code: Select all
UPDATE "Ingredient Free Of" SET "ID" = CASE "ID" WHEN 'YES' = '1' WHEN 'NO' = '2' WHEN 'N/A' = '3' ELSE '???' END;
- Press the Execute button
- You should a response back, that command was successful.
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;
- Press the Execute button
- You should a response back, that command was successful.
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;
- Press the Execute button
- You should a response back, that command was successful.
- Press the Close button
- From the Menu, choose: View -> Refresh Tables
- Review the contents of the new table, to confirm everything is as you expect. Save the file when you exit.
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.
Re: Query to Change Values
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
Re: Query to Change Values
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
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
Re: Query to Change Values
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.
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
Re: Query to Change Values
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.
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.
Re: Query to Change Values
The column that makes the row unique is "INGREDIENT NUMBER".
OpenOffice 4.1.1 on Windows 7
Re: Query to Change Values
In a post above, you said:
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') ?
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?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').
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') ?
Re: Query to Change Values
"ID PIC NUMBER" should be the output column name.
OpenOffice 4.1.1 on Windows 7
Re: Query to Change Values
Please try either ( or both ) of the following Queries, to return the 'calculated' values you desire.
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.
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"
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"
Code: Select all
Where "Ingredient Free Of"."ID PIC NUMBER" = :Enter_ID_PIC_NUMBER
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.