Deleting repetitive items not with same key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Deleting repetitive items not with same key

Post by crazycoders »

Hey there,

I have a database driven massmailing tool for one of my client. A lot of users create dupe accounts and subscribe to the mailing list. When building a list of people to send to i select it based on the id of the account cause i wish to build stats about who the email was sent to and opened it.

In all cases, i don't want to refactor my database, nor my code except for the sql string i'm using, so here goes:

table mvg_users contains:
- id int auto pk
- contact_email varchar(??)

table massmail_sentto:
- id int auto pk
- massmailid int
- user int
- flags tinyint

I'm currently calling my transfer like this:

Code: Select all

INSERT INTO forum_massmails_sentto (massmail_id, user, sent) SELECT '.$massmail['massmail_id'].' AS massmail_id, id AS user, '.MASSMAIL_STATE_UNSENT.' AS sent FROM mvg_users WHERE contact_flagsemail != 0
This selects all users that want to receive an email in the system.

I want it to filter out dupes based on the email. The only way i saw this possible was to create a select statement with a groupby to detect dupes after the insert and then loop the result and DELETE FROM massmail_sentto WHERE userid = ?? where ?? = a list of id's built from the GROUP BY i just did.

Thats surely not the only way, i just don't know how i could do it apart from that.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Deleting repetitive items not with same key

Post by Jade »

Can't you just pull all the email's distinctly? That way you won't send to the same email twice. That won't make up for the idiots who are using two different emails, but hey, maybe they want it sent to both places:

http://www.sql-tutorial.com/sql-distinct-sql-tutorial/
Post Reply