MySQL Update during CSV import 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 e开发者_Go百科xample, 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!
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.
精彩评论