If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update field based on current value
I need to update all values in a database column to new values. The
way it works is basically: if = abc replace with 123 if = bcd replace with 234 if = cde replace with 345 if = def replace with 456 Now the values used above are just for example it's not really pattern orientated. I can create four update queries for this, however was hoping there was a way to do this in one shot. Is there a single update query that could update the source values with new values based on rules like the above? Thanks. JR |
#2
|
|||
|
|||
Update field based on current value
IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...))) HTH; Amy "JR" wrote in message oups.com... I need to update all values in a database column to new values. The way it works is basically: if = abc replace with 123 if = bcd replace with 234 if = cde replace with 345 if = def replace with 456 Now the values used above are just for example it's not really pattern orientated. I can create four update queries for this, however was hoping there was a way to do this in one shot. Is there a single update query that could update the source values with new values based on rules like the above? Thanks. JR |
#3
|
|||
|
|||
Update field based on current value
On 5 Mar 2006 12:44:55 -0800, "JR" wrote:
I need to update all values in a database column to new values. The way it works is basically: if = abc replace with 123 if = bcd replace with 234 if = cde replace with 345 if = def replace with 456 Now the values used above are just for example it's not really pattern orientated. I can create four update queries for this, however was hoping there was a way to do this in one shot. Is there a single update query that could update the source values with new values based on rules like the above? Amy's suggestion is certainly one way if you don't have *too* many values (nested IIF's will eventually be too complicated to handle). Back up your database first, just in case of problems - update queries are NOT reversible! The next step up is to use the Switch() function: it takes arguments in pairs, and if the first member of the pair is True it will return the second argument and quit. That is, you could run an Update query updating the field to Switch([field] = "abc", 123, [field] = "bcd", 234, [field] = "cde", 345, [field] = "def", 456, ... etcetera, True, [Field]) The last value ensures that the field will be updated to its current value (left unchanged in other words) if it doesn't match any of the choices. The next step up from *there* is to create a translation table with two fields, the old and new values. Make the old value the Primary Key of this table. Then create an Update query joining your current table to the update table, change it to an Update query, and update to [TranslationTable].[NewValue] using your actual table and fieldname. The square brackets are required (otherwise it will update all records to the text string "TranslationTable.NewValue"!!!) John W. Vinson[MVP] |
#4
|
|||
|
|||
Update field based on current value
Create a table with the old values and the new values.
Then link that table to the existing table on the existing value to the old value. Now make an update the updates the fields to the new value An SQL statement would look like the following. UPDATE TableOriginal INNER JOIN TableNewValues ON TableOriginal.TheField = TableNewValues.TheOldValues SET TheField = TableNewValues.TheNewValues Amy Blankenship wrote: IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234', IIF(tblName[FieldName] = ...etc...))) HTH; Amy "JR" wrote in message oups.com... I need to update all values in a database column to new values. The way it works is basically: if = abc replace with 123 if = bcd replace with 234 if = cde replace with 345 if = def replace with 456 Now the values used above are just for example it's not really pattern orientated. I can create four update queries for this, however was hoping there was a way to do this in one shot. Is there a single update query that could update the source values with new values based on rules like the above? Thanks. JR |
#5
|
|||
|
|||
Update field based on current value
Correction to SQL
UPDATE TableOriginal INNER JOIN TableNewValues ON TableOriginal.TheField = TableNewValues.TheOldValues SET TheField = [TableNewValues].[TheNewValues] I forgot the all important square brackets in the update clause. John Spencer wrote: Create a table with the old values and the new values. Then link that table to the existing table on the existing value to the old value. Now make an update the updates the fields to the new value An SQL statement would look like the following. UPDATE TableOriginal INNER JOIN TableNewValues ON TableOriginal.TheField = TableNewValues.TheOldValues SET TheField = TableNewValues.TheNewValues Amy Blankenship wrote: IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234', IIF(tblName[FieldName] = ...etc...))) HTH; Amy "JR" wrote in message oups.com... I need to update all values in a database column to new values. The way it works is basically: if = abc replace with 123 if = bcd replace with 234 if = cde replace with 345 if = def replace with 456 Now the values used above are just for example it's not really pattern orientated. I can create four update queries for this, however was hoping there was a way to do this in one shot. Is there a single update query that could update the source values with new values based on rules like the above? Thanks. JR |
#6
|
|||
|
|||
Update field based on current value
Thanks...I tend to forget that there's an SQL version of the Select Case
statement. "John Vinson" wrote in message news On 5 Mar 2006 12:44:55 -0800, "JR" wrote: I need to update all values in a database column to new values. The way it works is basically: if = abc replace with 123 if = bcd replace with 234 if = cde replace with 345 if = def replace with 456 Now the values used above are just for example it's not really pattern orientated. I can create four update queries for this, however was hoping there was a way to do this in one shot. Is there a single update query that could update the source values with new values based on rules like the above? Amy's suggestion is certainly one way if you don't have *too* many values (nested IIF's will eventually be too complicated to handle). Back up your database first, just in case of problems - update queries are NOT reversible! The next step up is to use the Switch() function: it takes arguments in pairs, and if the first member of the pair is True it will return the second argument and quit. That is, you could run an Update query updating the field to Switch([field] = "abc", 123, [field] = "bcd", 234, [field] = "cde", 345, [field] = "def", 456, ... etcetera, True, [Field]) The last value ensures that the field will be updated to its current value (left unchanged in other words) if it doesn't match any of the choices. The next step up from *there* is to create a translation table with two fields, the old and new values. Make the old value the Primary Key of this table. Then create an Update query joining your current table to the update table, change it to an Update query, and update to [TranslationTable].[NewValue] using your actual table and fieldname. The square brackets are required (otherwise it will update all records to the text string "TranslationTable.NewValue"!!!) John W. Vinson[MVP] |
#7
|
|||
|
|||
Update field based on current value
On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship"
wrote: Thanks...I tend to forget that there's an SQL version of the Select Case statement. Well, that's yet another alternative - but that's in SQL/Server T-SQL, not in JET's peculiar dialect. The Switch() function is a VBA function which can be called from a query, not native Access/JET SQL. John W. Vinson[MVP] |
#8
|
|||
|
|||
Update field based on current value
Maybe that's why I forget it. My SQL has to work from outside Access in
most cases "John Vinson" wrote in message ... On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship" wrote: Thanks...I tend to forget that there's an SQL version of the Select Case statement. Well, that's yet another alternative - but that's in SQL/Server T-SQL, not in JET's peculiar dialect. The Switch() function is a VBA function which can be called from a query, not native Access/JET SQL. John W. Vinson[MVP] |
#9
|
|||
|
|||
Update field based on current value
Switch works outside Access, Amy. See the following URL ...
http://brenreyn.blogspot.com/ -- Brendan Reynolds Access MVP "Amy Blankenship" wrote in message ... Maybe that's why I forget it. My SQL has to work from outside Access in most cases "John Vinson" wrote in message ... On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship" wrote: Thanks...I tend to forget that there's an SQL version of the Select Case statement. Well, that's yet another alternative - but that's in SQL/Server T-SQL, not in JET's peculiar dialect. The Switch() function is a VBA function which can be called from a query, not native Access/JET SQL. John W. Vinson[MVP] |
#10
|
|||
|
|||
Update field based on current value
Cool work. Thanks for taking the time.
-Amy "Brendan Reynolds" wrote in message ... Switch works outside Access, Amy. See the following URL ... http://brenreyn.blogspot.com/ -- Brendan Reynolds Access MVP "Amy Blankenship" wrote in message ... Maybe that's why I forget it. My SQL has to work from outside Access in most cases "John Vinson" wrote in message ... On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship" wrote: Thanks...I tend to forget that there's an SQL version of the Select Case statement. Well, that's yet another alternative - but that's in SQL/Server T-SQL, not in JET's peculiar dialect. The Switch() function is a VBA function which can be called from a query, not native Access/JET SQL. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update a Foeld based on another Field | Dominic | General Discussion | 10 | January 5th, 2006 04:36 PM |
Update a field in a query based on a user input | RT | Running & Setting Up Queries | 4 | July 6th, 2005 09:10 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
How to loop through a recordset and update the field of a recordset or delete current record | Karen Middleton | Running & Setting Up Queries | 1 | January 4th, 2005 10:30 AM |
Text Form Field Ref in Footer Won't Update on Screen | StarWine | General Discussion | 3 | December 6th, 2004 06:17 PM |