CODEWITHSUNDEEP
mysqlsqldatabase
Mix Austria
Mix Austria

Reputation: 935

Update Quantity to subtract 1

Update product.tblproductinformation 
SET Quantity = (Quantity-1) 
where(Select iProduct.ProductID 
      from tblindividualproduct as iProduct 
      INNER JOIN tblproductinformation as pInfo ON iProduct.Code = pInfo.Code) = @p1"

i want to update my quantity to subtract 1. I also included inner join because my where is in another table. i got an error:

You cant specify target table 'tblproductinformation' for update in FROM clause

what's wrong?

Upvotes: 1

Views: 547

Answers (1)

eggyal
eggyal

Reputation: 125945

You can use the multiple-table UPDATE syntax to join the tables directly:

UPDATE tblproductinformation AS pInfo
  JOIN tblindividualproduct  AS iProduct ON iProduct.Code = pInfo.Code
SET    pInfo.Quantity = pInfo.Quantity - 1
WHERE  iProduct.ProductID = @p1

Upvotes: 2

Related Questions