MrTV
asked on
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
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 ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window