Don't worry, I've just twigged that I hadn't got the WHERE clause in place.
If any body is interested in this capability, then here is the process to update products in your products table by batch processing from something like a CSV file.
1. Create an import table. The code for my import table is:
DROP TABLE IF EXISTS `_import_products`;
CREATE TABLE `_import_products` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ProductSKU` varchar(50) DEFAULT NULL,
`ProductPrice` float(12,2) DEFAULT NULL,
`ProductWeight` float(12,2) DEFAULT NULL,
`ProductWidth` decimal(12,4) DEFAULT NULL,
`ProductHeight` decimal(12,4) DEFAULT NULL,
`ProductLength` decimal(12,4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2684 DEFAULT CHARSET=latin1;
2. Import the updated prices to the import table (I use Sequel Pro to handle imports etc).
3. Merge the two tables with this query:
UPDATE ps4_products
LEFT JOIN _import_products ON (_import_products.ProductSku = ps4_products.ProductSKU)
SET ps4_products.ProductPrice = _import_products.ProductPrice, ps4_products.ProductWeight = _import_products.ProductWeight, ps4_products.ProductWidth = _import_products.ProductWidth, ps4_products.ProductHeight = _import_products.ProductHeight, ps4_products.ProductLength = _import_products.ProductLength
WHERE _import_products.ProductSku = ps4_products.ProductSKU;
Obviously, this works for my import table, and I have added fields to the products table to allow for product dimensions. It will need to be adjusted to suit your requirement.
HTH
Mat
