sql statement mysql joint

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where claimdetail.claimrefid ='23' SET product.ProductQuantity = product.Produc' at line 8

the code is work but I want to add conditon I want to deduct only specific bill so i add
where claimdetail.claimrefid ='23'



UPDATE product
JOIN
(
SELECT productcode, SUM(quantity) SUMClaim
FROM claimdetail
GROUP BY productcode
) claims on (claims.productcode = product.ProductId    )
where claimdetail.claimrefid ='23'
SET product.ProductQuantity = product.ProductQuantity - claims.SUMClaim

UPDATE product
JOIN
(
SELECT productcode, SUM(quantity) SUMClaim
FROM claimdetail
GROUP BY productcode
) claims on (claims.productcode = product.ProductId    )

SET product.ProductQuantity = product.ProductQuantity - claims.SUMClaim





CREATE TABLE IF NOT EXISTS `claimdetail` (
  `claimid` int(11) NOT NULL AUTO_INCREMENT,
  `claimrefid` int(11) NOT NULL,
  `productcode` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `productnameshow` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `productname` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `mark` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `remark` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `chargeprice` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`claimid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=15 ;



CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductId` varchar(80) NOT NULL,
  `ProductName` varchar(255) DEFAULT NULL,
  `ProductType` varchar(255) DEFAULT NULL,
  `ProductPrice` decimal(15,2) NOT NULL,
  `ProductQuantity` int(14) unsigned NOT NULL,
  `LoShef1` decimal(10,0) NOT NULL,
  `Lostae1` decimal(10,0) NOT NULL,
  `qty1` int(11) unsigned NOT NULL,
  `qty2` int(11) unsigned NOT NULL,
  `LoShef2` int(10) DEFAULT NULL,
  `Lostae2` int(10) DEFAULT NULL,
  `mfbarcode` enum('y','n') NOT NULL DEFAULT 'y',
  `oldbar` varchar(80) DEFAULT NULL,
  `berakable` enum('y','n') NOT NULL DEFAULT 'n',
  `childcode` varchar(50) DEFAULT NULL,
  `breakableamount` int(9) DEFAULT NULL,
  `BarCodeOn` enum('y','n') NOT NULL,
  `useradd` varchar(30) NOT NULL,
  `DateTime` datetime DEFAULT NULL,
  `TimeStamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `productService` enum('y','n') DEFAULT 'n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ProductId` (`ProductId`),
  UNIQUE KEY `ProductId_3` (`ProductId`),
  KEY `ProductId_2` (`ProductId`),
  KEY `ProductName` (`ProductName`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=932 ;

Select all Open in new window