MySQL Update during CSV import question

Question

Okay, so I have this CSV file of products and all the products have an ID number. I am going to import this CSV into an existing (empty) database table where the columns in the database are named the exact same name as the first-row columns in the CSV file.

There is one extra column in the DB called URI. I need to build this during (or after is okay too) the import of the CSV file, based on info in the CSV file.

For example, I know the URI is going to start with-

http://foo.com/prodinfo.asp?number=

I need to append to basically build the URI and insert into that last field so that the URI looks like-

"http://foo.com/prodinfo.asp?number=" . $item_number . "&id=123456"

The first string and the last string will be the same every iteration, only the item_number will changed based on that column in the DB.

Now the question. I can easily write a PHP script to do the update for me, however, I don't know enough about MySQL to know whether it can be done during the import of the CSV or if there's some easy MySQL syntax I can run in SQLyog or something to do it without having to write/run a script each time?

Okay, I think that explains it, thanks!

Answer 1

Assuming your table is called table, the item_number column is called number, and that the URI column is called URI, you can issue the following SQL statement right after you are done importing the CSV:

UPDATE table SET URI=CONCAT('http://foo.com/prodinfo.asp?number=',number,'&id=123456');
COMMIT;

If you don't want to do this manually after each import, you can have MySQL run the statement automatically as part of a trigger.