Announcement

Collapse
No announcement yet.

How avoid duplicate record if you have a table with "autoincrement" ID column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How avoid duplicate record if you have a table with "autoincrement" ID column

    I did lot of test but still have the problem that I'm not able to avoid duplicate record.
    The existing index do not block duplicate (???).
    I don't know how to fix it .

    My mySQL table has an ID field
    memberID int(11) No None AUTO_INCREMENT
    ....
    ....
    and these two INDEXes (1st primary and 2nd unique)
    PRIMARY__________________BTREE Sė No memberID 1 A
    key_Name_Year_Meccan_____BTREE Sė No contactLastName 1 A
    _____________________________________________membe rName 1 A
    _____________________________________________year_ card_actual 1 A
    _____________________________________________natio nal_federationID 1 A

    This is my query for upload new records or update existing ones (with same index key_Name_Year_Meccan). No way it wants to work ;-((

    $sql_insert_update_members = "
    INSERT IGNORE INTO members(
    officeID , section_ID , registration_typeID , new_renewal , memberName , contactLastName , userName , titleID , borndate , bornplace , borncountry , sexID , citizenshipID , phone , fax , mobile01 , mobile02 , addressLine1 , addressLine2 , e_mail , fbname , twname , skype , image , city , state , postalCode , country , vatcode , residence , fiscalcode , national_federationID , medical_date , subscr_date , year_card , year_card_actual , member_card , license_card_01 , license_card_02 , license_card_03 , license_card_04 , license_card_05 , affiliated01 , affiliated02 , affiliated03 , affiliated04 , affiliated05 , category_01 , category_02 , shirt_name , shirt_number , shirt_size , pants_size , helmet_size , boots_size , gloves_size , insert_date
    )

    SELECT
    officeID , section_ID , registration_typeID , new_renewal , memberName , contactLastName , userName , titleID , borndate , bornplace , borncountry , sexID , citizenshipID , phone , fax , mobile01 , mobile02 , addressLine1 , addressLine2 , e_mail , fbname , twname , skype , image , city , state , postalCode , country , vatcode , residence , fiscalcode , national_federationID, medical_date , subscr_date , year_card , year_card_actual , member_card , license_card_01 , license_card_02 , license_card_03 , license_card_04 , license_card_05 , affiliated01 , affiliated02 , affiliated03 , affiliated04 , affiliated05 , category_01 , category_02 , shirt_name , shirt_number , shirt_size , pants_size , helmet_size , boots_size , gloves_size , NOW()
    FROM members_fim_csv

    ON DUPLICATE KEY
    UPDATE
    members.new_renewal = members_fim_csv.new_renewal ,
    members.borndate = members_fim_csv. borndate ,
    members.sexID = members_fim_csv.sexID ,
    members.citizenshipID = members_fim_csv.citizenshipID ,
    members.phone = members_fim_csv.phone ,
    members.fax = members_fim_csv.fax ,
    members.mobile01 = members_fim_csv.mobile01 ,
    members.addressLine1 = members_fim_csv.addressLine1 ,
    members.e_mail = members_fim_csv.e_mail ,
    members.city = members_fim_csv.city ,
    members.residence = members_fim_csv.residence ,
    members.subscr_date = members_fim_csv.subscr_date,
    members.update_date = NOW()
    ";

  • #2
    First of all, what do you want to do: INSERT IGNORE ... or INSERT ... ON DUPLICATE KEY UPDATE ?
    I suppose you want to update in case the record exists, so remove your IGNORE and see what the result is.
    I would recommend to start with a plain INSERT...

    If you don't get an error message and the command inserts duplicate rows, there is something wrong with your indices. Simple as that.
    Are both table definitions identical (field_type, field_size), are you sure both sides have the same content? (space character!).

    jsb

    Comment


    • #3
      Hi,
      thanks,
      Yes, you are right but ... you know...to be sure ... I've exagerate!!!
      Incredible but, after a testing day, the problem was connected to a csv field that was imported with a blank space before the number .... an so it did not finded the same value and it went to insert a new , ...almost, identical record.
      Now I've trimmed the field and now also index seems to work without making, as expected, any the duplicate.
      Based on your hind I deleted IGNORE.
      Just to know , why it worked also with both ??

      Comment


      • #4
        It's because the IGNORE statement ignores the error of a duplicate key and does not abort with an error message so the ON DUPLICATE KEY UPDATE finishes the operation.
        Since you want to update anyway there is no need for the IGNORE statement because the ODKU clause also checks for duplicate keys (as the name suggests).

        jsb

        Comment


        • #5
          attachment

          2 first images are about what happens and last one it's my goal
          Attached Files

          Comment

          Working...
          X