CODEWITHSUNDEEP
phpmysql
Kyle
Kyle

Reputation: 3042

Mysql checking for duplicates when inserting data

I have two mysql tables sent and queue. I insert members into the queue table. However I don't want to insert that same member again--if they're in the sent table. They were already sent an invitation.

How can I check if the member inserted into queue doesn't exist in sent ?

Upvotes: 0

Views: 263

Answers (3)

HBublitz
HBublitz

Reputation: 680

If it's your application principle, that there must not be identical queue entries, why not add a unique constraint (on member_email, member_id or whatever the identifier might be) to your queue table? You can then insert your data with REPLACE INTO ... which in fact means: If there's an existing value with the same primary key or unique key, it will be deleted before inserting the new one.

REPLACE INTO queue (member_email, queue_detail)
VALUES ('already_existing_email', 'some_data');

This would virtually UPDATE the entry of already_existing_email with 'some_data'.

If there's no entry *already_existing_email*, data will be inserted.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425251

INSERT
INTO    queue (member)
SELECT  $member
FROM    dual
WHERE   $member NOT IN
        (
        SELECT  member
        FROM    sent
        )

Upvotes: 3

Treffynnon
Treffynnon

Reputation: 21553

Try to select the user from the sent table and if there are no rows returned then insert into the queue table. Otherwise do nothing. For any more depth than that you would need to disclose more of your schema and what denotes a user as unique (email address?).

Upvotes: 1

Related Questions