Solved

mysql sql statement

Posted on 2011-03-06
9
275 Views
Last Modified: 2012-05-11
UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE product.ProductId = claimdetail.quantity

the code is work but plese see picture 4712893150132 it deduct only first row
delclaim.png
0
Comment
Question by:teera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 250 total points
ID: 35053407
I think this is wrong
.... WHERE product.ProductId = claimdetail.quantity

It should be
WHERE product.ProductId = claimdetail.ProductID
right ?
0
 
LVL 16

Assisted Solution

by:santoshmotwani
santoshmotwani earned 250 total points
ID: 35053411
can you please give your table structure for
product & claimdetail


0
 

Author Comment

by:teera
ID: 35053424
hi experts
product.ProductId = claimdetail.quantity

this is correct
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 16

Expert Comment

by:santoshmotwani
ID: 35053446
try this
UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE cast ( product.ProductId as int)  = claimdetail.quantity
0
 

Author Comment

by:teera
ID: 35053473
hi antoshmotw…
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 'int) = claimdetail.quantity' at line 1
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 35053484
UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE cast ( product.ProductId as int(80) )  = claimdetail.quantity

try this plz
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35053491
I still suspect something wrong in there WHERE condition.
UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE product.ProductId = claimdetail.quantity

Make sure you are storing 'ProductID' in 'Quantity' column of 'claimdetail' table !!!
How it could be linked ?

See claimtable have some other columns that stands for which project the quantity belongs to

Sorry if I am not clear

Again - Are you storing 'ProjectID' in Quantity column of claimdetails table ?

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35053594
Spelling mistake - I mean
Again - Are you storing 'ProductID' in Quantity column of claimdetails table ?
product.ProductId  = claimdetail.quantity 

Open in new window

?


0
 

Author Comment

by:teera
ID: 35065822
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
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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