Hi Dianne. Yes, I got it to work.
My original question was with respect to my client giving me a table full of products, but no prices. My client then gave me a price list with the product SKU code associated to the price. What I then did was a query that searched the database matching SKU codes in the products table with a table that I created which had the SKU code and price, and where the two matched exactly, it placed the price in the correct field.
What I suspect you are trying to do is populate the table from scratch though. I assume you are not using any kind of GUI for MySQL and that you are using the shell tool. I don't use the shell tool myself, I use Sequel Pro for Mac OSX and it works a treat.
To populate the products table with thousands of products in about 15 seconds I simply imported a CSV exported from the Excel Spreadsheet given to me by my client. I had to go through it and remove rogue characters such as degree symbols, apostrophes were changed to feet or foot marks ('), double apostrophes were changed to inch marks (") and so on.
To change rogue characters, hopefully you've got it, but I used InDesign as it afforded me better control over what I could search for due of it's 'grep' capabilities etc in the find/change dialogue. I copied all content from the spreadsheet, and pasted into InDesign. In InDesign, you'll find that each Excell cell is represented by a tab, and each row is defined by a newline... KEEP THESE! I could search tens of thousands of products and change them in seconds. You'll have to recognise your own problem characters etc but it works. Once I had completed my changes I simply copied it all and pasted into a new spreadsheet (to retain the old copy), and hey presto, job done. By keeping the tab and newline characters, this automatically splits your content back into cells and rows. I then saved it as a CSV. Simple!
Back in Sequel Pro, I imported the CSV, married the CSV columns to that of the database, and assuming that you have picked up ALL rogue characters, it will import successfully. if it doesn't import, it will stop near where it fails, check your MySQL products table and this will give you an idea of where to start looking as it will have stopped some records before importing that rogue character.
If you do have InDesign, save the work you've done in that as a failed import is when it is useful to go back to.
I was able to tidy up the content and populate the database with thousands of products in less than a day. Realising what I could now do, I did much, much more with InDesign which took more than a week of time where I was able to create all sorts of strings for various other reasons which won't apply to you as I have changed my store so much since I first bought it, but once you get your head around it, it really will help you solve many problems.
No complex SQL queries needed. Once you're done, check your data hasn't shifted in some way. Once or twice I found that product codes, names, prices etc had moved to the next record putting the whole table out making the data useless, but this was invariably due to a rogue character!
Does this long-winded explanation help?
If not, then I assume you're trying to update an already populated products table which requires an equally long-winded explanation!
Mat
