mysql sql statement

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
teeraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
I think this is wrong
.... WHERE product.ProductId = claimdetail.quantity

It should be
WHERE product.ProductId = claimdetail.ProductID
right ?
0
 
santoshmotwaniConnect With a Mentor Commented:
can you please give your table structure for
product & claimdetail


0
 
teeraAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
santoshmotwaniCommented:
try this
UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE cast ( product.ProductId as int)  = claimdetail.quantity
0
 
teeraAuthor Commented:
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
 
santoshmotwaniCommented:
UPDATE product SET product.ProductQuantity = (product.ProductQuantity - claimdetail.quantity) WHERE cast ( product.ProductId as int(80) )  = claimdetail.quantity

try this plz
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
Spelling mistake - I mean
Again - Are you storing 'ProductID' in Quantity column of claimdetails table ?
product.ProductId  = claimdetail.quantity 

Open in new window

?


0
 
teeraAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.