473,379 Members | 1,278 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

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 1658
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Alistair | last post by:
IIS, Access 2000 I have a page that tabulates data from a DB, this data is items for sale, private ads. is there a way that at the same time it can automatically delete entries that are...
19
by: Sandeep Grover | last post by:
Hi, If I do another delete on an object which has been deleted earlier, then how is the system expected to behave ? Is it an unpredictable behavior ?? Thanks Sandeep
2
by: Joe | last post by:
Hey, I'm going to give some background on my situation in case anyone can point out a way around my problem altogether... for the problem itself, please skip to the bottom of the post. thanks....
10
by: nick_faye | last post by:
Hi guys, i'm still a newbie in using MS Access and in VB programming. I am using DAO connection from my VB to access the entries on my MS Access table. I am having trouble in editting and...
3
by: Bernard Lebel | last post by:
Hello, Is there an option or a way to allow the selection of multiple entries in the Listbox widget? I could not find any, and would like to allow the end user to select multiple entries. ...
5
by: Gareith Stanley | last post by:
Hi there I have an access form that allows the user to browse records (< and > buttons) sorted by another field within that table (eg, all entries under a staff group, all entries under a student...
29
by: Jon Slaughter | last post by:
Is it safe to remove elements from an array that foreach is working on? (normally this is not the case but not sure in php) If so is there an efficient way to handle it? (I could add the indexes to...
0
by: sajithkahawatta | last post by:
i want to delete a selected row from the database.the entries of database is shown by a gridview and i do <OnRowDeleting="GridView1_RowDeleting"> in source code. i select and bind as follows....
2
by: peridian | last post by:
Hello, I understand what DISTINCT and DISTINCTROW do in Access, and that they don't exist in other SQL versions. Could somebody explain the thinking behind why you need the DISTINCTROW keyword...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.