Today I encountered a problem. There are N article records stored in the Access database. Now I want to replace some of the contents in these articles. The first thing we think of is to go to the website backend to modify the articles one by one. If there are 1,000 articles, then modify them 1,000 times in the website backend. It is hard to imagine what kind of workload this is. In fact, SQL statements can be used to replace content in batches in the Access database, and the problem can be solved in just one sentence. The following are two methods to solve this problem.
Method 1: Modify through the query analyzer in the Access database (I am using Access 2003 here)
1. Open the Access database that needs to be modified
2. Click "Query" in the database "Object"
3. Click "Create query in design view"
4. Close the "Show Table" window in the interface that appears
5. Click the "View" menu and select "SQL View". The query window will appear. You can enter SQL statements here.
6. Directly enter the following SQL statement:
Update table SET field=replace(field, "original character", "replace character")
You can change the above red characters according to the actual situation. Here, assuming that the table is biao, the field is content, the original character is xiazai., and the replacement character is down1., the corresponding SQL statement is as follows:
Update biao SET content=replace(content,"xiazai.","down1.")
7. Click the exclamation mark in the toolbar and run it.
Method 2: Use ASP programs to replace characters in batches. The above code has the problem of character length limitation. There is no limit to this.
The ASP program code is given directly below, and you will know it at a glance:
'The database connection code is omitted here
Dim rs,sql,text
Set rs=("")
sql="Select content From biao"
sql,conn,1,3
Do While Not
text=Replace(rs("content"),"xiazai.","down1.")
rs("content")=text
Loop
Set rs=Nothing
Method 1: Modify through the query analyzer in the Access database (I am using Access 2003 here)
1. Open the Access database that needs to be modified
2. Click "Query" in the database "Object"
3. Click "Create query in design view"
4. Close the "Show Table" window in the interface that appears
5. Click the "View" menu and select "SQL View". The query window will appear. You can enter SQL statements here.
6. Directly enter the following SQL statement:
Update table SET field=replace(field, "original character", "replace character")
You can change the above red characters according to the actual situation. Here, assuming that the table is biao, the field is content, the original character is xiazai., and the replacement character is down1., the corresponding SQL statement is as follows:
Copy the codeThe code is as follows:
Update biao SET content=replace(content,"xiazai.","down1.")
7. Click the exclamation mark in the toolbar and run it.
Method 2: Use ASP programs to replace characters in batches. The above code has the problem of character length limitation. There is no limit to this.
The ASP program code is given directly below, and you will know it at a glance:
Copy the codeThe code is as follows:
'The database connection code is omitted here
Dim rs,sql,text
Set rs=("")
sql="Select content From biao"
sql,conn,1,3
Do While Not
text=Replace(rs("content"),"xiazai.","down1.")
rs("content")=text
Loop
Set rs=Nothing