Link to home
Create AccountLog in
Avatar of MrTV
MrTVFlag for Thailand

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
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 ;

Open in new window

Avatar of Shinesh Premrajan
Shinesh Premrajan
Flag of India image

hope this helps
UPDATE product
SET product.ProductQuantity = product.ProductQuantity - claims.SUMClaim

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Thomasian
Thomasian
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account