Solved

MySQL: Duplicate Issue with INSERT INTO... From...

Posted on 2011-09-09
1
258 Views
Last Modified: 2012-05-12
The problem with my query is it adds duplicates.

I do not want an item to be added it it was already added.
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) SELECT '22', `ID` from `Member` WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` = '1' AND `SMSMember` != '1'

Open in new window

0
Comment
Question by:hankknight
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36511940
you have 2 options:
* use the ON DUPLICATE KEY.. syntax (resp the REPLACE syntax):
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

* or you write your query so it does not return duplicates, resp does not insert rows that are already in the table
INSERT INTO `Group_Members` (`GroupID`,`MemberID`) 
SELECT '22', `ID` from `Member` m
WHERE `UserSuspended` != '1' AND `PremPlusMember` != '1' AND `PremWebMember` = '1' AND `SMSMember` != '1'
 AND NOT EXISTS( select null from Group_Members g WHERE g.GroupID = 22 and g.MemberID = m.ID )

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
password_verify with prepared statements 10 36
MYSQL/PHP inserting 1969-12-31 instead of NULL 12 46
Insert with SET how to handle join 6 57
Amazon Redshift 2 26
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now