Update field based on current value

[JR]

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

 

[Amy Blankenship]

IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...)))

HTH;

Amy

 

[John Vinson]

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]

 

[John Spencer]

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

IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...)))

HTH;

Amy

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

 

[John Spencer]

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.

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

IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...)))

HTH;

Amy

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

 

[Amy Blankenship]

Thanks...I tend to forget that there's an SQL version of the Select Case
statement.

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]

 

[John Vinson]

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]

 

[Amy Blankenship]

Maybe that's why I forget it. My SQL has to work from outside Access in
most cases

 

[Brendan Reynolds]

Switch works outside Access, Amy. See the following URL ...

http://brenreyn.blogspot.com/

 

[Amy Blankenship]

Cool work. Thanks for taking the time.

-Amy

Switch works outside Access, Amy. See the following URL ...

http://brenreyn.blogspot.com/