Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql statement mysql joint

Posted on 2011-03-07
2
Medium Priority
?
583 Views
Last Modified: 2012-05-11
#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

0
Comment
Question by:teera
2 Comments
 
LVL 17

Expert Comment

by:Shinesh Premrajan
ID: 35066080
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

0
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 35066165
UPDATE product
JOIN
(
SELECT productcode, SUM(quantity) SUMClaim
FROM claimdetail
where claimdetail.claimrefid ='23'
GROUP BY productcode
) claims on (claims.productcode = product.ProductId    )

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

Open in new window

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question