title: Oracle Merge ignore duplicates
body: <p>I have a table accessible via an oracle database link, which I'm attempting to pull into a local database table because reasons.</p>
  
  <pre><code>MERGE INTO MEMBERSHIPS LOCAL
  USING (
    SELECT DISTINCT
      REMOTE.GROUP_NAME        "GROUP_NAME",
      REMOTE.USER_ACCOUNT      "USERNAME",
      REMOTE.SOME_OTHER_COLUMN "COL3"
    FROM MEMBERSHIPS@link REMOTE
  ) REMOTE
  ON (
    REMOTE.GROUP_NAME = LOCAL.GROUP_NAME AND
    REMOTE.USERNAME   = LOCAL.USERNAME
  )
  WHEN MATCHED THEN
    UPDATE SET
      LOCAL.COL3       = REMOTE.COL3
      LOCAL.UPDATED_AT = sysdate
  WHEN NOT MATCHED THEN
    INSERT (ID, GROUP_NAME, USERNAME, COl3, CREATED_AT, UPDATED_AT)
    VALUES (MEMBERSHIPS_SEQ.NEXTVAL, REMOTE.GROUP_NAME, REMOTE.USERNAME, REMOTE.COl3, sysdate, sysdate)
  </code></pre>
  
  <p>Most unfortunately, the owner of the original database has not lost much sleep worrying about data integrity, so in the 3 millionish rows, there are 71 duplicates, which blows up my Unique Index on Group Name, Username. The merge will process if I remove the uniqueness constraint, however these rows will then blow up on a subsequent execution of the query with a <code>ORA-30926: unable to get a stable set of rows in the source tables</code>.</p>
  
  <p>This is the sort of thing that will get run on a daily basis, so I need to  find a way to ignore duplicates</p>
  
  <p>EDIT:</p>
  
  <p>I would have thought the distinct would have took care of the problem for me, but it doesn't. I still get duplicates:</p>
  
  <pre><code>SELECT DISTINCT
  REMOTE.GROUP_NAME,
  REMOTE.USER_ACCOUNT
  COUNT(*)
  FROM MEMBERSHIPS@link REMOTE
  GROUP BY
  REMOTE.GROUP_NAME,
  REMOTE.USER_ACCOUNT
  HAVING COUNT(*) &gt; 1;
  </code></pre>
  
  <p>Shows 71 GROUP_NAME/USER_ACCOUNT combinations that are still duplicated</p>