469,275 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

delete multiple DB entries?

A different question though. Is it possible to delete duplicate entries,
where the email address is the same, and only keep one?
i.e..

I got say 4 DB entries, all with the same email address, and I want to
delete 3. How do I do this?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
Jul 19 '05 #1
6 1517
RS.OPEN "SELECT * FROM MyTable WHERE myMail = 'M******@SPAM.BE' ORDER BY
myMail"

RS.MoveNext (skip the first)

DO WHILE NOT RS.EOF
RS.Delete
RS.MoveNext
LOOP
Jul 19 '05 #2
Rudi Ahlers wrote:
A different question though. Is it possible to delete duplicate
entries, where the email address is the same, and only keep one?
i.e..

I got say 4 DB entries, all with the same email address, and I want to
delete 3. How do I do this?


What is the database, please?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #3
Rudi Ahlers wrote:
A different question though. Is it possible to delete duplicate
entries, where the email address is the same, and only keep one?
i.e..

I got say 4 DB entries, all with the same email address, and I want to
delete 3. How do I do this?

What database please? Type and version.

Oh, and a little more information about the table structure would be
helpful. How do you want to decide which record to keep, and which ones to
discard?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #4
Hi Bob

I thought about it, and here it is.

I have an excel file, with an odd 60.000 records. Then, I have a MySQL DB
with 24000 records. Now, those 24000 records also appear in the .xls file.
Thus, I only want to upload the "new" records. I have an MSSQL 2000 DB as
well, and though I'd use that to do the database sorting, but then it would
need to be exported again.

Unless there is a way of importing all the stuff. When it comes to a record
that already exists, to simply skip it, and go on with the next one?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ed**************@tk2msftngp13.phx.gbl...
Rudi Ahlers wrote:
A different question though. Is it possible to delete duplicate
entries, where the email address is the same, and only keep one?
i.e..

I got say 4 DB entries, all with the same email address, and I want to
delete 3. How do I do this?

What database please? Type and version.

Oh, and a little more information about the table structure would be
helpful. How do you want to decide which record to keep, and which ones to
discard?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 19 '05 #5
I know next-to-nothing about MySQL, but:
1. Since MySQL is the destination i would not involve MS SQL.
2. If possible, you should flag the records in excel that have already been
downloaded. Perhaps, create a new worksheet to contain only new email
addresses and import from that worksheet.
3. You should import the data into an intermediate table and then use the
following SQL statement to insert only the records that don't already exist
into the actual destination table (this example will be very generic since
you did not provide the details about your table structure):
Insert into emailtable(<columnlist>)
Select <columnlist>
FROM imported_data i
WHERE NOT EXISTS
(Select * FROM emailtable
WHERE email_address = i.email_address)

This sql statement will work in MS SQL. I do not know it MySQL supports this
syntax. You should find a MySQL forum or newsgroup and ask there if you
can't get it working.

Bob Barrows

Rudi Ahlers wrote:
Hi Bob

I thought about it, and here it is.

I have an excel file, with an odd 60.000 records. Then, I have a
MySQL DB with 24000 records. Now, those 24000 records also appear in
the .xls file. Thus, I only want to upload the "new" records. I have
an MSSQL 2000 DB as well, and though I'd use that to do the database
sorting, but then it would need to be exported again.

Unless there is a way of importing all the stuff. When it comes to a
record that already exists, to simply skip it, and go on with the
next one? "Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ed**************@tk2msftngp13.phx.gbl...
Rudi Ahlers wrote:
A different question though. Is it possible to delete duplicate
entries, where the email address is the same, and only keep one?
i.e..

I got say 4 DB entries, all with the same email address, and I want
to delete 3. How do I do this?

What database please? Type and version.

Oh, and a little more information about the table structure would be
helpful. How do you want to decide which record to keep, and which
ones to discard?

Jul 19 '05 #6
ok, thanx. I see what you say, and that SQL statement is what I was looking
for.

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:OS**************@TK2MSFTNGP09.phx.gbl...
I know next-to-nothing about MySQL, but:
1. Since MySQL is the destination i would not involve MS SQL.
2. If possible, you should flag the records in excel that have already been
downloaded. Perhaps, create a new worksheet to contain only new email
addresses and import from that worksheet.
3. You should import the data into an intermediate table and then use the
following SQL statement to insert only the records that don't already exist
into the actual destination table (this example will be very generic since
you did not provide the details about your table structure):
Insert into emailtable(<columnlist>)
Select <columnlist>
FROM imported_data i
WHERE NOT EXISTS
(Select * FROM emailtable
WHERE email_address = i.email_address)

This sql statement will work in MS SQL. I do not know it MySQL supports this
syntax. You should find a MySQL forum or newsgroup and ask there if you
can't get it working.

Bob Barrows

Rudi Ahlers wrote:
Hi Bob

I thought about it, and here it is.

I have an excel file, with an odd 60.000 records. Then, I have a
MySQL DB with 24000 records. Now, those 24000 records also appear in
the .xls file. Thus, I only want to upload the "new" records. I have
an MSSQL 2000 DB as well, and though I'd use that to do the database
sorting, but then it would need to be exported again.

Unless there is a way of importing all the stuff. When it comes to a
record that already exists, to simply skip it, and go on with the
next one? "Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ed**************@tk2msftngp13.phx.gbl...
Rudi Ahlers wrote:
A different question though. Is it possible to delete duplicate
entries, where the email address is the same, and only keep one?
i.e..

I got say 4 DB entries, all with the same email address, and I want
to delete 3. How do I do this?

What database please? Type and version.

Oh, and a little more information about the table structure would be
helpful. How do you want to decide which record to keep, and which
ones to discard?


Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Sandeep Grover | last post: by
3 posts views Thread by Bernard Lebel | last post: by
29 posts views Thread by Jon Slaughter | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.