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

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

lets pretend have supermarket. got table called sales every record 1 article), if scan 3 articles have 3 rows next columns: articleid , amount amount 1. , have table called articles have columns: articleid , availableamount.

when sale done need count records same in sales table , update availableamount availableamount subtracted sum of each article.

i'm thinking dont know if im thinking right:

update articles set availableamount = availableamount - ( select articleid,count(*) sales grouping articleid having count(*) > 1 ) articleid in(select distinct articleid sales)

this query correct, but

the subquery must homecoming 1 column, having count(*) > 1 not create sense, and

the subquery must homecoming 1 value, need correlated subquery:

update articles set availableamount = availableamount - (select count(*) sales articleid = articles.articleid) articleid in (select articleid sales)

sqlite