Asp delete duplicates in Database
This code delete duplicate records in MSAcces database.
Original Author: Pierre Morissette
Inputs
The user must define 4 variables (lines 29 to 32) in order to configure the code for his/her personnal use.
Assumptions
The author is not responsible for any lost of your data.
Read the code comments before using it.
Works only with dsn-less connexion.
Returns
This code returns the number of record in the database and the number of deleted duplicate records.
Side Affects
The author suggest you to use the code first on a copy of your database to prevent unwanted deletion.
API Declarations
Feel free to use the code as long as you leave the author name and email adress.
Code
<%@ LANGUAGE = "VBScript" %>
<% option explicit %>
<%server.scripttimeout=600%>
<%
'IMPORTANT user must define those variables CAREFULLY
Dim mydatabase,mytable,myprimarykey,myfield
mydatabase="../../db/signets.mdb"
mytable="mes_sites"
myprimarykey="id"
myfield="url"
dim SQL,conn,rs,nb,i,nbtot,valurl,nbdup,nbdup2,valret,validdup,arr,nbarr
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DBQ=" & Server.Mappath(mydatabase) & ";Driver={Microsoft Access Driver (*.mdb)};"
%>
<%
sql="select count(*) as nb from "
sql=sql & mytable
Set RS = conn.Execute(SQL)
'calcul du nombre de fiches, count the number of records in table
arr=""
nbtot=rs("nb")
nbtot=cint(nbtot)
response.write nbtot
response.write " fiches / files"
response.write "
"
sql="select "
sql = sql & myfield
sql=sql & ","
sql=sql & myprimarykey
sql=sql & " from "
sql=sql & mytable
sql=sql & " order by "
sql=sql & myfield
set rs=conn.execute(sql)
'selectionner la valeur de myfield(i), select the value of field myfield # i
for i=0 to (nbtot- 1)
rs.movefirst
rs.move(i)
valurl= rs.fields(myfield)
' v?®rifier si valeur dupliqu?®e, check if the value of the field is a duplicate value
if valurl=valret then
validdup= rs.fields(myprimarykey)
' ajouter l'id de la fiche ?á la liste des duplicats, add id value to the array if duplicate value
arr= arr & validdup
arr = arr & ","
else
end if
'remind the last value to compare to next one
valret = valurl
next
rs.close
set rs=nothing
'?®crire la liste des duplicats, writes the list of all id value that contains duplicates
response.write "Records that contains duplicate data in field myfield"
response.write "
"
'now use the array created to delete records
'create array
if arr = "" then
response.write "There is no duplicate record."
else
arr=left(arr,len(arr)-1)
response.write arr
arr=split(arr,",",-1,1)
nbarr = ubound(arr)
nbarr=nbarr + 1
response.write "
"
response.write "Number of duplicate records :"
response.write nbarr
response.write "
"
'number of records to duplicate
for i=0 to nbarr-1
SQL = "delete from "
sql=sql & mytable
SQL = SQL & " WHERE "
sql=sql & myprimarykey
sql = sql & " ="
sql=sql & arr(i)
Set RS = conn.Execute(SQL)
next
Response.write "All the duplicate records are now deleted."
end if
set rs=nothing
conn.close
set conn = nothing
%>
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.