Renaming fields from one table value to another

[Tskutnik]

I want to automate a find and replace process to standardize names in a table, using the value from one table to replace another. The code I have is not quite working.

Background:
· Table [Checking] field [Description] has the source text field that I like to change/standardize
· Table [Rename] field [Description] has the text string used to search the [Checking].[Description] values. The search should contain wildcard logic so e.g. a [Rename].[Description] value of “Mobil” would find a [Checking].[Description] value of “Mobil 123”, or “Mobil 234”, or “Mobil123456 AB”.
· Table [Rename] field [NewDescription] is the new value used. If [NewDescription] = “Mobilx”, then the “Mobil 123”, or “Mobil 234”, or “Mobil123456 AB” would all be changed to “Mobilx”.

I have the following code which partially works:

UPDATE checking, rename SET [Checking].[Description] = [Rename].[NewDescription]
WHERE ((([Checking].[Description]) like "*" & [Rename].[Description] &"*") );


Problem is the original [Checking].[Description] value is not deleted in full so the replacement [Checking].[NewDescription] value is not correct.


e.g. "Mobil12345 AB" might be changed to "Mobilx AB" instead of “Mobilx” (some of the original string remains).

Can someone tell me the SQL for this?

 

[plog]

First, before you do this, make a backup of your database so when my instructions don't work you have a good dataset to revert to.

I think you need Rename in the UPDATE clause:

UPDATE Rename, Checking SET [Checking].[Description] = [Rename].[NewDescription]
WHERE ((([Checking].[Description]) like "*" & [Rename].[Description] &"*") );

This is because you haven't linked your tables with an INNER JOIN--which is ok. This process needs to compare every value in Rename.Description with every value in Checking.Description so not having an INNER JOIN will work, its just to compensate you need Rename in the UPDATE clause.

And just to satisfy the pedants that follow--you could also do this with an INNER JOIN. You would INNER JOIN Rename ON Checking using your existing WHERE clause. Cue the arguments about why the INNER JOIN is the proper way to go.

 

[Tskutnik]

Thanks for the reply. Very helpful.
I want to do this the right way so I joined the [Checking].[Description] and [Rename].[Description] fields. But I’m having a few issues with the actual code when trying to convert it to the Inner Join method. Hate to ask but can you post what the full code would look like using the Inner Join function?
I expect that will close this thread.

 

[plog]

I don't know that an INNER JOIN is the right way to do this. Both methods will work.

Generally UPDATE queries shouldn't be something that you use consistently in a database, just a once-off type of thing to get your data into the shape it should be--which sounds like you are doing. You don't plan on running this UPDATE query often/consistently do you? Just to make the data correct this once, after that the data will be good, right?

 

[Tskutnik]

Thanks
Can you tell me what the correct code would be using the inner join? I'll do some tests and see which work better.
Appreciate the advice.

 

[plog]

I think this is it:

UPDATE Checking SET [Checking].[Description] = [Rename].[NewDescription]
INNER JOIN Rename ON ((([Checking].[Description]) like "*" & [Rename].[Description] &"*") );

 

[RainLover]

Why not create a query in the query grid. It would only take less than a minute rather than trying to get the syntax correct.

 

[RainLover]

A quick question as I am not sure exactly what you are saying. Are you trying to Change the Value within the Fields or do you want to change the actual field name because this is what you are saying you want to do.

 

[Tskutnik]

Trying to change the values in the fields