And yes, once you set it up, INSERT IGNORE would allow your query to simply skip the insertion of any records that already exist in the table. Something else to look at would be the INSERT ... ON DUPLICATE KEY UPDATE syntax, depending on your version of MySQL ( >= 4.1 ) http://dev.mysql.com/doc/mysql/en/INSERT.html
I'm not yet sure yet what to make of your last situation, where you are merging addresses into one group. About the bounce count, presumably that is not necessarily the same value for each instance of an email address across different groups? Is the bounce count the only field that would differ between the two duplicate records?
Wes
On Jul 20, 2004, at 10:36 PM, Scott Haneda wrote:
on 7/20/04 4:10 PM, Wesley Furgiuele at [EMAIL PROTECTED] wrote:
Rather than make the email address column unique, since you want to allow multiple email address instances, you can make a unique index based on email_address+group. Wouldn't that help, or is that still too simple for your situation?
I don't fully understand here, hope you can help. Is making a unique index
on 2 columns something I would run perhaps on a schedule to deal with this,
or is this something I set once and it is just maintained? If it is
something that is on schedule, or triggered say after a bulk import by the
user, when I send in that ALTER how do I also tell mysql to keep the one
dupe email address with the highest bounce count?
Or, perhaps this is something I set once, then I would use INSERT IGNORE and
the dupes would not be allowed and would simply gracefully fail? Can this
work In a transaction environment, where I would
START TRANSACTION;
repeat with aEmail in uploaded file
INSERT IGNORE into addresses etc etc etc
end repeat
COMMIT;
I will also have one more case that needs this treatment as well, say there
are 2 groups of emails, lets call them "family" and work". I will be
allowing the user to merge those into one group, something like:
UPDATE addresses SET group='family' WHERE group='work' AND user_id ='123',
would I still be able to get the duplicates out in this scenario as well?
(note: group is not a string, I just used it as one in this example)
Maybe this would be a better case to use a temp table, select both the
"family" and "work" into a temp table, then somehow remove only those that
have a bounce count of zero, or in the case all dupes bounce count are zero,
simply remove the newest entry.
Ugh, this is making my head spin :-)
-- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
