Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: SQL Export & Importing

    Posted - 05/31/2012 : 10:45:55
    exporting

    INSERT INTO `tbl_phonedir` (`firstname`, `lastname`, `extension`, `mailbox`, `loccode`, `email`, `record_id`) VALUES
    ('test', 'test', '4227', '4227', 'ACS', 'test@test.org', 334);

    edit to import

    INSERT INTO `tbl_phonedir` (`firstname`, `lastname`, `extension`, `mailbox`, `loccode`, `email`, `record_id`) VALUES
    ('edit', 'edit', '4227', '4227', 'ACS', 'edit@edit.org', 334);

    i get a #1062 - Duplicate entry '334' for key 'PRIMARY'

    i need to update 1000 things i am trying to export make the changes and import with out deleting everything first i am super new to this can any one help i am using phpadmin for import and export

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because your question seems to be about MySQL, I'm moving your thread (question) from the Microsoft SQL Server forum to the MySQL forum.

    A Primary Key only permits one row with a given value for that key, that is part of the definiton of a Primary Key. Your PK will only allow a single row at any given time with any given value of record_id, such as 334.

    You have several choices, but my guess would be that an UPDATE statement would be your best choice.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2012
    Posts
    3
    update sounds good i did try to play around with it but it was not working could someone show me that me sql file should look like

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For your example:
    Code:
    UPDATE `tbl_phonedir`
       SET `email` = 'edit@edit.org'
       WHERE  334 = `record_id`
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that message tells us that column record_id is the primary key
    A primary key must be unique
    so you cannot have two records with a record_id of 334

    either
    insert a record with a different value of record_id
    OR
    up[date the existing row to refelct the new data
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2012
    Posts
    3
    UPDATE tbl_phonedir SET (`firstname`, `lastname`, `extension`, `mailbox`, `loccode`, `email`, `record_id`) VALUES
    ('name', 'name', '4227', '4227', 'ACS', 'same@update.org', 334);

    can i do something like that i am trying to just edit what i exported so can import the changes

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    look at the SQL update syntax
    MySQL :: MySQL 5.0 Reference Manual :: 13.2.10 UPDATE Syntax

    understand that if you dont' put a where clause on the end of the updtae it will atlter every row.

    ferinstance
    UPDATE mytable SET mycolumncalledcomments = 'Blah' where Surname = 'yoshi5'
    will set a column called mycolumncalledcomments ti 'balh' where the column called userid = 'yoshi'
    update mytable SET Surname = 'Clone'
    will set every row's column to 'clone' as there is no where clause limiting the rows selected. this is one of the reasons that in a realtional database every row should have a unique key so that any row can be updated individually

    update mytable set acolumn = 42 where record_id = 334

    PHPMyAdmin provides the skeleton SQL for you.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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