#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 productJOIN(SELECT productcode, SUM(quantity) SUMClaimFROM claimdetailGROUP BY productcode) claims on (claims.productcode = product.ProductId )SET product.ProductQuantity = product.ProductQuantity - claims.SUMClaimCREATE 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 ;
Open in new window