[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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

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
hankknight
Asked:
hankknight
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now