August 25th, 2012, 12:27 AM
Update SQL table from a csv file
I have a csv file that has a list of ID numbers like this (the second column in the csv file is not used):
I have a SQL database with a table that has a field called "photo", and also a field called "ID".
If the csv file has a matching ID number, I would like to insert the ID number into the photo field. If there is no matching ID number in the csv file, then I want to insert a blank "" into the photo field for that record.
So, how can I do this in SQL? I thank you in advance for your help.
August 25th, 2012, 05:30 AM
the first thing you have to do is load your csv into a table
after that, it's a joined update, where you update your photos table from the table you've uploaded from the csv
August 25th, 2012, 02:07 PM
Can I get some details on how to do that? This would eventually have to be automated too, so it can run every night. Thanks.
August 25th, 2012, 02:41 PM
for details, i'd need your table layouts
could you describe each table's column names please
August 25th, 2012, 05:32 PM
So the table that I am trying to update is called Students. It has a field called "ID", and a field called "Photo", and also many other fields such as birth date but they are not relevant here. One thing to note is there might sometimes be more than one record in the table with the same ID number, however they are the same student so the same value would be inserted into the Photo field for both IDs. There are never two different students with the same ID.
The CSV file contains the student ID number, which was created by doing a directory listing of all photos we have on file.
The ultimate purpose here is to update the photo field, so in our database we know we have a photo for those students.
I would want to automate this to probably run every night. Thank you.
August 25th, 2012, 09:24 PM
SET photo =
FROM students t1
JOIN csvtable t2
ON t2.id = t1.id