Find and Replace query from a 2nd table.

[Nabil Bugaighis]

Hi, I'd be grateful for any assistance.

I am using Access 2003, but I also have Access 2007.

I have a table (Categories) with a column "descriptio ns". This column has html code including several urls per field (1000 rows not all unique. some fields might be duplicated in several rows)

I have a second table "redirects" with two columns, "Old-URL" and "New-URL" (600 rows)

the two tables are completely independent.

I want to run a find and replace on the first table to find EACH "OLD-URL" from the second table in the "descriptio ns" column of the first table(there might be 100 instances in different rows) and replace it with the "new-URL" from the second table.

I started doing this manually, but after 20 rows my wrist is hurting :(

[patjones]

I would suggest looking into building an update query in Query Design view. You would add the column "descriptio ns" to the design grid. The "Update To" entry would contain the "New-URL" column. The "Criteria" entry would contain the "Old-URL" column (this will cause the query to do an update only when there is a match between the "descriptio n" column and "Old-URL" column).

When you do an update to a table, be sure to make a copy of the table beforehand; then, if something goes wrong during the update, you won't have done any irreversible damage.

Hopefully this is enough to get you started...

Pat

[Nabil Bugaighis]

Pat,
Thank you for the reply. But this would not work.
The problem is that the field "descriptio n" has a lot of info in it with mulitple urls.
In the example below there are TWO old urls, each would need to be found and replaced with the new-url1 and new-url2 from the "redirects" table.

<table border="0" width="600" id="table1" cellspacing="0" height="156"><t r><td><img border="0" src="images/xxx.jpg" width="600" height="175"></td></tr><tr><td class=topbanner >blah blah blah blah<br>
<span class="Content" >Explore item 1.&nbsp;.<br>< a href="/url1-category-109.htm">old-URL1</a>&nbsp;&nbsp; ▪ &nbsp;<a href="/item2-category-343.htm">old-URL2<a> </span></td></tr></table>

[Mariostg]

How about something like:
UPDATE YourTable
SET YourField=repla ce("YourField", "YourOldURL","Y ourNewURL")

[Nabil Bugaighis]

Thank you for the suggestion.

I tried this, but it does not work. This what I tried:

UPDATE categories
SET description=rep lace("descripti on",[redirects].[oldurl],[redirects].[newurl])

I need it to go through the "redirects" table row by row, picking each [redirects].[oldurl], looking in the [Categories].[description] column, and replacing each instance of [redirects].[oldurl] with [redirects].[newurl]

note that the [Categories].[description] fields contains other info besides OLDRUL that should not be changed.

[patjones]

Hi Nabil,

Since I am not particularly knowledgeable about HTML, I don't really understand the sample data that you provided.

What I would say though is that the data in that column may be breaking database normalization rules. Generally, one attempts to store data in columns in the smallest (atomic) units possible. To take a simple example, it would violate normalization rules to store two phone numbers in a single phone number column.

With that being said, is there any way that you can break the data in that column out into simpler units? If so, the methods that I and Mariostq provided (which are actually one and the same thing) would be more effective.

Pat

[Mariostg]

Oh I see. I that case you probable want to loop this in a VBA Function.

Function SearchReplace()
    Dim rs as new ADODB.Recordset
    Dim cn as new ADODB.Connection
    Dim sql as String
    Set cn=CurrentProject.Connection

    sql="SELECT oldurl, newurl FROM redirects"
    rs.open sql, cn
    rs.MoveFirst
    Do While Not rs.EOF and Not rs.BOF
        sql = "UPDATE categories" & _
            " SET description=replace('description'," & rs!oldurl & "," & rs!newurl)"
        DoCmd.RunsSQL sql
        rs.MoveNext
    Loop
    rs.close
    Set rs=Nothing
    Set cn=Nothing
End Function

I don't have MS Access with me at the moment so I cannot do spell check. But this is the basic idea. It will only change the oldurl, not the whole field content.