Count columns in a table where columns are same and then subtract from a column in another table

Vote count:

0

Lets pretend that we have a supermarket. We got a table called Sales where every record is one article), so if we scan 3 articles we will have 3 rows with following columns: ArticleId and Amount where amount Always is 1. And then we have a table called Articles which have columns: ArticleId and AvailableAmount.

When the sale is done we need to Count records that are the same in Sales table and then update AvailableAmount with AvailableAmount subtracted with the sum of each article.

I'm thinking something like this but i dont know if im thinking right:

UPDATE Articles
SET
   AvailableAmount = AvailableAmount - (
      Select ArticleId,Count(*) From Sales Group by ArticleId HAVING Count(*) > 1
   )
WHERE 
ArticleId in(Select distinct ArticleId FROM Sales)

asked 2 mins ago

Henrik

734

Count columns in a table where columns are same and then subtract from a column in another table