MySQL – Updating Table Contents from Another Table

MySQLmysql-5.5mysql-5.6xml

I want to update the products table from my store db from a temp table.. the production table 'Products' needs to update all columns from the temp table 'tmpprod' based on StockCode and not change any other columns in the Products table

UPDATE Products
JOIN tmpprod
ON Products.StockCode = tmpprod.StockCode
    SET Products.CategoryCode = tmpprod.CategoryCode,
        Products.CategoryName = tmpprod.CategoryName,
        Products.SubcategoryID = tmpprod.SubcategoryID,
        Products.SubcategoryName = tmpprod.SubcategoryName,
        Products.BarCode = tmpprod.BarCode,
        Products.Weight = tmpprod.Weight,
        Products.ManufacturerSKU = tmpprod.ManufacturerSKU,
        Products.ProductName = tmpprod.ProductName,
        Products.WarrantyLength = tmpprod.WarrantyLength,
        Products.Manufacturer = tmpprod.Manufacturer,
        Products.DBP = tmpprod.DBP,
        Products.DBP5 = tmpprod.DBP5,
        Products.RRP = tmpprod.RRP,
        Products.StandardRRP = tmpprod.StandardRRP,
        Products.AvailabilityTotal = tmpprod.AvailabilityTotal,
        Products.AvailabilityAdelaide = tmpprod.AvailabilityAdelaide,
        Products.AvailabilityBrisbane = tmpprod.AvailabilityBrisbane,
        Products.AvailabilitySydney = tmpprod.AvailabilitySydney,
        Products.AvailabilityMelbourne = tmpprod.AvailabilityMelbourne,
        Products.AvailabilityPerth = tmpprod.AvailabilityPerth,
        Products.ImageUrl = tmpprod.ImageUrl,
        Products.ImageFilename = tmpprod.ImageFilename,
        Products.DescriptionLink = tmpprod.DescriptionLink

and…

CREATE TABLE `Products` (
    `StockCode` text, 
    `CategoryCode` text, 
    `CategoryName` text, 
    `SubcategoryID` int(11) DEFAULT NULL, 
    `SubcategoryName` text, 
    `BarCode` text, 
    `Weight` double DEFAULT NULL, 
    `ManufacturerSKU` text, 
    `ProductName` text, 
    `WarrantyLength` int(11) DEFAULT NULL, 
    `Manufacturer` text, 
    `DBP` int(11) DEFAULT NULL, 
    `DBP5` int(11) DEFAULT NULL, 
    `RRP` double DEFAULT NULL, 
    `StandardRRP` double DEFAULT NULL, 
    `AvailabilityTotal` text, 
    `AvailabilityAdelaide` text, 
    `AvailabilityBrisbane` text, 
    `AvailabilitySydney` text, 
    `AvailabilityMelbourne` text, 
    `AvailabilityPerth` text, 
    `ImageUrl` text, 
    `WebsiteUrl` text, 
    `ImageFilename` text, 
    `DescriptionLink` text, 
    `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP, 
    `ForSale` int(11) NOT NULL DEFAULT \'0\'
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

and…

'CREATE TABLE `tmpprod` (
    `StockCode` text,
    `CategoryCode` text,
    `CategoryName` text,
    `SubcategoryID` int(11) DEFAULT NULL, 
    `SubcategoryName` text, 
    `BarCode` text, 
    `Weight` double DEFAULT NULL, 
    `ManufacturerSKU` text, 
    `ProductName` text, 
    `WarrantyLength` int(11) DEFAULT NULL, 
    `Manufacturer` text, 
    `DBP` int(11) DEFAULT NULL, 
    `DBP5` int(11) DEFAULT NULL, 
    `RRP` double DEFAULT NULL, 
    `StandardRRP` double DEFAULT NULL, 
    `AvailabilityTotal` text, 
    `AvailabilityAdelaide` text, 
    `AvailabilityBrisbane` text, 
    `AvailabilitySydney` text, 
    `AvailabilityMelbourne` text, 
    `AvailabilityPerth` text, 
    `ImageUrl` text, 
    `WebsiteUrl` text, 
    `ImageFilename` text, 
    `DescriptionLink` text, 
    `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

Best Answer

InnoDB really needs a PRIMARY KEY on each table. You must add it to the real table; it is less important for the temp table.

Don't blindly use TEXT for every column; use things like VARCHAR(10) or VARCHAR(100). The codes and skus probably have known max length. Names rarely get over (60). Etc. Use TEXT only for really long strings.

If "Availability" is a true/false flag, use TINYINT.

Now for the real problem -- How does UPDATE know which row to change?? Do you have some column (or columns) that uniquely identifies the row so that you clobber the correct one? That should be the PRIMARY KEY.

Without the index, it is scanning the entire table repeatedly. Very slow.

Related Question