Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Location
    New York
    Posts
    5

    Unanswered: How to Import CSV data and define primary column

    This is my first time doing something like this so would appreciate any help...

    I want to update an existing table(Table 1) from values of another table (Table 2) so what I have is:

    Table 1
    aCol1
    aCol2
    aCol3
    aCol4
    aCol5

    Table 2
    bCol1
    bCol2
    bCol3
    bCol4
    bCol5

    Column "aCol1" is an auto number filed and it is also the primary key.
    Columns "aCol3" and "bCol3" is actually a field that has the unique data that I don't want to change and would just want update all field around that one.

    Example:

    Table 1
    Number | Age | Last Name | Color | Time
    2 | 27 | Smith | Blue | May
    7 | 18 | Joees | Pink | April

    Table 2
    Number | Age | Last Name | Color | Time
    | 27 | Smith | Green | June
    6 | 16 | Joees | Orange |

    Expected end result after uploading "Table 2"
    Table Merged
    Number | Age | Last Name | Color | Time
    2 | 27 | Smith | Green | June
    6 | 16 | Joees | Orange | April


    So in this case the controlling field should be last name and not the first auto Number field that is also the primary key. Is the only way to controll this is by changing the primary key of Table one to "Last name" right before uploading the CSV or is there a way to specify which shoudl be primary in the CSV file?


    Thanks!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am not sure what you are asking for here? If you have two tables with the data already populated then use an UPDATE statement as follows:

    Code:
    UPDATE table1,
           table2
    SET    table1.age = table2.age,
           table1.color = table2.color,
           table1.time = table2.time
    WHERE  table1.lastname = table2.lastname;
    Attempting to load a CSV file and have this perform the UPDATE instead of an INSERT is not possible unless you use a REPLACE statment. However, this means that is first deletes the existing record based on a duplicate key and then inserts the new record from the CSV file. This will result in you losing the auto number field.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2012
    Location
    New York
    Posts
    5
    You right, uploading a CSV gave a duplicate error and would require deleting everything before the upload.

    However, I would still need to export table 2 and make make some excel changed (vlookup) because Table two is currently actually composed of 2 tables which I would need to properly merge before uploading to table 1


    Table 2........Table A
    col1 < Auto number
    col2 < Will be all "1"
    col3 < col3
    col4 < Will be all "0"
    ..................Table B
    col4 < col1

    After manipulating the exported CSV's in excel mu plan would be to:
    1) Clone Table 1 (just structure and no data) and call it "Table 1Merge"
    2) Upload my fixed CSV into the "Table 1Merge" table.
    3) Use your suggested UPDATE statement to merge "Table 1Merge" into "Table 1"



    EDIT:
    How would I edit that UPDATE statement so that if there is no matching last name value in table 1, it will just create a new row instead of updating?

    Thanks!
    Last edited by dbcook; 04-21-13 at 12:01.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What you should do is create a staging table. This is a table where you load all the data from the CSV file. The table should always be emptied before uploading the data. Once the data has been loaded you can then use either an UPDATE statement as I mentioned before or INSERT statement as follows:

    Code:
    INSERT INTO table1(age, lastname, color, time) SELECT age, lastname, color, time from table2
    ON DUPLICATE KEY UPDATE age = table2.age, color = table2.color, time = table2.time;
    What this does is if there is a duplicate the insert converts itself into an update statement using the duplicate key to update the record that exists.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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