469,150 Members | 1,951 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

Find and Replace query from a 2nd table.

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 "descriptions". 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 "descriptions" 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 :(
Sep 16 '10 #1
6 1865
931 Expert 512MB
I would suggest looking into building an update query in Query Design view. You would add the column "descriptions" 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 "description" 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...

Sep 17 '10 #2
Thank you for the reply. But this would not work.
The problem is that the field "description" 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"><tr><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>
Sep 17 '10 #3
332 100+
How about something like:
UPDATE YourTable
SET YourField=replace("YourField","YourOldURL","YourNe wURL")
Sep 17 '10 #4
Thank you for the suggestion.

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

UPDATE categories
SET description=replace("description",[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.
Sep 17 '10 #5
931 Expert 512MB
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.

Sep 17 '10 #6
332 100+
Oh I see. I that case you probable want to loop this in a VBA Function.

Expand|Select|Wrap|Line Numbers
  1. Function SearchReplace()
  2.     Dim rs as new ADODB.Recordset
  3.     Dim cn as new ADODB.Connection
  4.     Dim sql as String
  5.     Set cn=CurrentProject.Connection
  7.     sql="SELECT oldurl, newurl FROM redirects"
  8.     rs.open sql, cn
  9.     rs.MoveFirst
  10.     Do While Not rs.EOF and Not rs.BOF
  11.         sql = "UPDATE categories" & _
  12.             " SET description=replace('description'," & rs!oldurl & "," & rs!newurl)"
  13.         DoCmd.RunsSQL sql
  14.         rs.MoveNext
  15.     Loop
  16.     rs.close
  17.     Set rs=Nothing
  18.     Set cn=Nothing
  19. 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.
Sep 18 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by scorpion53061 | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.