I need to update all prices and dimensions with a batch process
Hi, I have been trying to get MySQL to update the prices of all products within the ps4_products table by batch processing it with a query, but I can't get it working. I know it's possible, but I am just doing something wrong and need help.
Here's how am I trying to do it. I have the ps4_products table and I have created a table called _import_products which has all of the up-to-date info in it. I am then trying to ask MySQL to merge the info of the two tables by matching the SKU. I am expecting each record to have updated prices, weight, dimensions etc, but what I am getting is a new series of records in addition to the existing records.
Here's my query:
SELECT ProductSku, COUNT(ProductSku) _import_products FROM _import_products
GROUP BY ProductSku;
UPDATE ps4_products
INNER JOIN _import_products ON (_import_products.ProductSku = ps4_products.ProductSKU)
SET ps4_products.ProductPrice = _import_products.ProductPrice;
SET ps4_products.ProductWeight = _import_products.ProductWeight;
SET ps4_products.ProductWidth = _import_products.ProductWidth;
SET ps4_products.ProductHeight = _import_products.ProductHeight;
SET ps4_products.ProductLength = _import_products.ProductLength;
Here's the structure of the tables:
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;
DROP TABLE IF EXISTS `ps4_products`;
CREATE TABLE `ps4_products` (
`ProductID` int(12) NOT NULL AUTO_INCREMENT COMMENT 'None',
`ProductSKU` varchar(50) DEFAULT NULL COMMENT 'Product Code/SKU',
`ProductBrand` varchar(255) DEFAULT NULL COMMENT 'Brand Name',
`ProductName` text NOT NULL COMMENT 'Product Description',
`ProductPrice` float(12,2) NOT NULL DEFAULT '0.01',
`ProductRetailPrice` float(12,2) DEFAULT NULL,
`ProductShipping` float(12,2) DEFAULT NULL,
`ProductWeight` float(12,2) DEFAULT '0.00',
`ProductWidth` decimal(12,4) DEFAULT NULL,
`ProductHeight` decimal(12,4) DEFAULT NULL,
`ProductLength` decimal(12,4) DEFAULT NULL,
`ProductCartDesc` text COMMENT 'Product Description',
`ProductShortDesc` text COMMENT 'Product Description',
`ProductLongDesc` text COMMENT 'Product Description',
`ProductThumb` varchar(100) NOT NULL DEFAULT '' COMMENT 'Product Code/SKU',
`ProductImage` varchar(100) NOT NULL DEFAULT '' COMMENT 'Product Code/SKU',
`ProductUpdateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ProductStartDate` datetime DEFAULT NULL,
`ProductEndDate` datetime DEFAULT NULL,
`ProductStock` float(12,2) DEFAULT '0.00',
`ProductLive` tinyint(1) DEFAULT '1',
`ProductUnlimited` tinyint(1) DEFAULT '1',
`ProductDigital` varchar(250) DEFAULT NULL,
`ProductMaxDownloads` int(12) DEFAULT NULL,
`ProductNonTaxable` int(1) DEFAULT '0',
`ProductTitle` text,
`ProductMetaKeywords` varchar(255) DEFAULT NULL,
`ProductMetaDescription` varchar(255) DEFAULT NULL,
`ProductCategories` text COMMENT 'Category',
PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=6220 DEFAULT CHARSET=utf8;
As you can see, I've added a few column to the table where sizes are kept.
I'd appreciate your help with this.
Thanks.
Mat