Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Inserting records from one table to another ignoring duplicates

    I have the following code that will insert records into table 1 from table 2 if it doesn't exist. However, I have found out through much testing that there can be duplicates in the source table (table 2). When a duplicate is found, I get a Unique constraint error and no records are inserted.

    INSERT INTO ap_inv a (inv_num,po_num) SELECT inv_num,po_num FROM vn_inv v WHERE NOT EXISTS (SELECT null FROM ap_inv a WHERE a.inv_num=v.inv_num AND a.po_num=v.po_num)

    What can I do to modify this statement to have duplicates ignored? I don't want to remove the duplicates.

    Thanks,
    SBR

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think you need either a distinct or a group by in your select to remove the duplicates.

    Alan

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I don't understand what you mean when you say 'I don't want to remove the duplicates'. Do you want to keep the duplicates (all the duplicates) or do you just want to keep one instance of each record, if the latter, which one would you want (first, last, the middle one, the second one, etc..) ?

  4. #4
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Inserting records from one table to another ignoring duplicates

    Yes, I want to keep all of what appears to be duplicates. I only want it to insert the inv_num and po_num into table 1 if it doesn't exist.

    To explain better (I hope), the records are not really duplicates. However there can be several records in table 2 that have the same inv_num and po_num. Table 1 has only 1 instance of an inv_num and po_num per record (no duplicates).

    I hope that makes sense.

    Thanks,
    SBR

  5. #5
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Inserting records from one table to another ignoring duplicates

    I fixed it. I added the word DISTINCT in my SELECT statement:

    INSERT INTO ap_inv a (inv_num,po_num) SELECT DISTINCT inv_num,po_num FROM vn_inv v WHERE NOT EXISTS (SELECT null FROM ap_inv a WHERE a.inv_num=v.inv_num AND a.po_num=v.po_num) AND v.duplicate IS null;

    Thanks for your help,

    SBR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •