0

I have three tables t1, t2 and t3.

What I want is to update table t1 with t1.Quantity= sum(t2.quantity) - sum(t3.quantity) where id= $_POST['id']

HOW TO WRITE QUERY FOR THIS.

I tried this one.. but its not working.

 INSERT INTO Products
   ( `ProductID`, `ProductName`, `TotalQuantity`,
     `TotalPrice`, `DateOfLastupdate` )
 values
  ( '$ProductID', '$ProductName', '$Quantity',
    '$TotalPrice', '$PurchaseDate' )
 ON DUPLICATE KEY
   UPDATE Products.TotalQuantity =
     ( select sum(Products_Purchased.Quantity) from Products_Purchased
       where ProductID = '$ProductID' )
     - ( select sum(Products_Sold.Quantity) from Products_Sold 
           where ProductID = '$ProductID' )
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
mona
  • 5
  • 4
  • if record is not exists,then it should be inserted else should be updated.. thats why i m using insert on duplicate key – mona Jan 16 '14 at 05:20

2 Answers2

0

May it helps

UPDATE table1, table2 SET table1.column1 = ( SELECT SUM( (SELECT constant FROM table3) + (SELECT table2.sum_number *** WHERE table2.table2_id1 = table1.id) ) ) WHERE table1.id = table2.table2_id1;

UPDATE table1 SET column1 = (SUM(table2{& table3} WHERE table2_id1 = id1) WHERE id1 = table2_id1

Community
  • 1
  • 1
vignesh.D
  • 766
  • 4
  • 18
0

try this

update products t1,
(select productid,sum(Products_Purchased.Quantity) as x from Products_Purchased
   group by productid having ProductID = '$ProductID' ) t2,
(select productid,sum(Products_Sold.Quantity) as y from Products_Sold 
   group by productid having ProductID = '$ProductID' ) t3
 set TotalQuantity=t2.x-t3.y where t1.ProductID = '$ProductID' 
      and t1.productid=t2.productid and t1.productid=t3.productid
Daniel Robertus
  • 1,100
  • 1
  • 11
  • 24