Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: Deleting Duplicate records

    I have two tables (Updates and Main). The 'Updates' table is imported from another database. I need to merge the two tables (simple enough) but when this happens it makes duplicates. The main table has every account number w/customer information that the Updates table has. So naturally when I merge the two tables I get duplicate records in the Main table. Is there a way to delete specific records?

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Deleting Duplicate records

    Something like

    ----------------------------
    INSERT INTO Main(Field1, Field2, ...., FieldN)
    SELECT Updates.Field1, Updates.Field2, ...., Updates.FieldN
    FROM Updates LEFT OUTER JOIN Main ON Updates.PK = Main.PK
    WHERE Main.PK IS NULL
    ----------------------------

    PK stands for the primary key. This query inserts the records from Updates that do not occur yet in Mains.

    Ad Dieleman.

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Thanks for your help.

    I enter the SQL statement like you have but I get an error. It just states error in SQL statement. I'm a little new at writing SQL statements so I'm sure I typed something wrong. I guess I have one question. In your statement you have: INSERT INTO Main(Field1, Field2, ...., FieldN), I have never seen the format 'tablename(field1,field2) like this. Do I actually type the table name and the field names or just the table name? Thanks in advance and sorry for my stupid questions.

  4. #4
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    Originally posted by cc3658
    Thanks for your help.

    ...(deleted)
    I guess I have one question. In your statement you have: INSERT INTO Main(Field1, Field2, ...., FieldN), I have never seen the format 'tablename(field1,field2) like this. Do I actually type the table name and the field names or just the table name? ...(deleted
    You can do both. You only type the table name if you want to insert values in all fields of the table, otherwise you need to sum up the fields like you stated.

    You made me doubt if Access accepts this kind of INSERT statements, Access doesn't always fully support each SQL-conforming statement.
    The following statement executed without errors (Access 2002), it was copied from the SQL window:

    INSERT INTO Tabel1 ( Veld1, Veld2 )
    SELECT Tabel2.Veld1, Tabel2.Veld2
    FROM Tabel2 LEFT JOIN Tabel1 ON Tabel2.Veld1=Tabel1.Veld1
    WHERE Tabel1.Veld1 IS NULL

    Ad Dieleman.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    does veld mean field, i hope so cause it's the only way it makes sence to me
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95
    Originally posted by m.timoney
    does veld mean field, i hope so cause it's the only way it makes sence to me
    Yes, I was lazy enough to accept the defaults in Dutch when constructing the tables and queries.

    Ad Dieleman.

  7. #7
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    thanks.

Posting Permissions

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