Updating fields in a mysql database

shobhanc has asked for the wisdom of the Perl Monks concerning the following question:

I need help for a small project which I have to complete very soon. The thing is i have data in Mysql with the following fields (fname, lname, email, state, zip, Group, mailed). There is one script which inserts data into all the fields except the fields 'Group' and 'mailed'(this is an enum). Now i have a CSV file with the same users which are in the database with all the fields. I should look up the database using the email and update appropriate fields, and if the email doesnt exist in the database i should insert the data. can anyone offer a solution to this.??

Comment on Updating fields in a mysql database

Replies are listed 'Best First'.
Re: Updating fields in a mysql database by Abigail-II (Bishop) on Jul 23, 2002 at 17:30 UTC
So, if I understand, the data in the CSV file is correct? Why don't you just delete all the data in the MySQL table, then read in the CSV file? MySQL can load data from CSV files. Otherwise (for instance if the CSV doesn't contain all users), use the DBI. It has drivers for both MySQL and CSV. Read from the CSV file, and for each record, do a delete/insert on the MySQL site. Abigail	[reply]
Re: Updating fields in a mysql database by BigD (Scribe) on Jul 24, 2002 at 09:08 UTC
Another option, using the DBI module (check the perldoc DBI for syntax), is read the information in your table, and as long as you read every entry, if the email doesn't exist, do an INSERT statetement, and if it exists and what you want is to update the information, execute an UPDATE statement using the email in the WHERE clause. (check any tutorial on SQL to check the syntax).	[reply]