Update query

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

How do I go about developing an update query to update the same column
in a table based on several criteria?

For example, in the first_name column I'd like to update Jon to
Jonathan, Ed to Edward, Dave to David, Frank to Franklin, etc.

Can a single update query do this? Using Access 2002.

Thanks.

Steve
 
Steve said:
How do I go about developing an update query to update the same column
in a table based on several criteria?

For example, in the first_name column I'd like to update Jon to
Jonathan, Ed to Edward, Dave to David, Frank to Franklin, etc.

Can a single update query do this? Using Access 2002.

Thanks.

Steve
Click to expand...

Yes, but somewhat akwardly.

UPDATE TableName
SET [TableName]![first_name] =
SWITCH([TableName]![first_name]="Jon","Jonathan",
[TableName]![first_name]="Ed","Edward",
[TableName]![first_name]="Dave","David")

Not sure if Switch has a practical or hard limit to the number of variations is
can handle.

It might be better to throw together a quick two column table with the new and
old names as columns and do an update based on a join between the existing table
and the new one.

Please test on a *copy* of your data.
 
Several methods. But probably the best is to build a table of equivalents and
then use that in your update query.

tblEquivalents
--fldCurrent (Jon, Ed, Dave, ...)
--fldNewValue(Jonathan, Edward, David, ...)

Now, you can use that to update your existing table by joining the Existing
table to the table of equivalents based on a join between the First_Name and
fldCurrent. The new value would be fldNewValue. The SQL would look something
like (UNTESTED SQL):

UPDATE ExistingTable As E INNER JOIN tblEquivalents as N
ON E.First_Name = N.fldCurrent
SET E.First_Name = N.fldNewValue

Other methods would use the Switch Function or nested iff statements and are not
nearly as flexible.
 
Back
Top