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:
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.
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?
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:
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.
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"
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"
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?
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:
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.