CODEWITHSUNDEEP
sql
Victor Tang
Victor Tang

Reputation: 5

update Sql Calculation statement

May i know how to update Sql Calculation statement ?

For example i want to update the sum tatal to the table

Below is the code

 SELECT p.Qty AS pQty,
       SUM(t.Qty) AS tQty,
       p.Qty - SUM(t.Qty) AS total ,
       p.PName
FROM tblTempTransaction t ,
     tblProducts p
WHERE t.UserID= 2
  AND t.OrderID=1
  AND p.pid = t.pid
GROUP BY t.UserID,
         p.qty ,
         p.PName ,
         t.OrderID

UPDATE tblProducts
SET qty = p.Qty - SUM(t.Qty)
WHERE t.UserID= 2
  AND t.OrderID=1
  AND p.pid = t.pid
GROUP BY t.UserID,
         p.qty ,
         p.PName ,
         t.OrderID

I will get the error from set qty = p.Qty - SUM(t.Qty)

May i know how to solve this ? Thanks.

Upvotes: 0

Views: 147

Answers (1)

zxc
zxc

Reputation: 1526

assuming that youre query is correct.. you forgot to put 'from' 

  UPDATE tblProducts
     SET qty = p.Qty - sq.total from (select SUM(t.Qty) as total from "table" as t
   WHERE t.UserID= 2 and t.OrderID=1 and p.pid = t.pid
GROUP BY t.UserID, p.qty ,p.PName ,t.OrderID) as sq

Upvotes: 1

Related Questions