Scott Haneda wrote:
<snip>
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.
What is user_id? You didn't mention it before. Will the user with user_id=123 be able to make a 'work' group and the user with user_id=456 also be able to make a 'work' group? That is, you have multiple lists (group) for each user (user_id). If that's the case, we need to alter the previous suggestion. You have to put the unique index on whatever combination of columns determines uniqueness. It now sounds as if you want each email address to appear no more than once per combination of user_id and group. In that case, you'll need a unique index on those *3* columns:
ALTER TABLE addresses ADD UNIQUE uge_idx (user_id, group, email_address);
Also, to be clear, there will be no duplicates, ever. You will not be able to add the index in the first place if there are duplicates already in the table. You'll have to clean out existing duplicates before you'll be able to add the unique index. Once the unique index is in place, you will not be able to create duplicates. If you try to insert a duplicate row, you'll get an error message unless you added IGNORE, which tells mysql to keep quiet.
This makes your UPDATE tricky. You cannot create a duplicate row with your UPDATE. So, if [EMAIL PROTECTED] is in user 123's work and family lists,
UPDATE addresses SET group='family' WHERE group='work' AND user_id ='123';
will fail for [EMAIL PROTECTED] You'll get an error message, unless you added IGNORE, but either way, [EMAIL PROTECTED] will still exist in group 'work', which may come as a surprise if you were expecting group 'work' to disappear as a result of that statement. You could fix that by adding
DELETE FROM addresses WHERE group='work' AND user_id ='123';
after the UPDATE.
Ugh, this is making my head spin :-)
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
