blah blah blah is here! blah blah » Close

0
Elliot_S
400
371 views

Updating product table with count of items sold

up0down
link

I have a Products table with columns:

ProductID INT PK
SoldCount INT


My other table, ItemsSold, contains items sold:

ProductID INT FK
Quantity INT

So this is a many to one relationship.

I want to update the Product.SoldCount field with the SUM of the rows in the ItemsSold table for each productID.

What would the sql look like?

sql-server sql update
  • asked 2 years ago
add comment
last answered 2 years ago

1 answers

Sort:
up1down
tick
link

You can update the products table based on the items sold in your ItemsSold table like this: 


UPDATE p

     SET p.soldCount = is.totalCount

FROM 

(

    SELECT productID, SUM(quantity) as totalCount

    FROM ItemsSold

    GROUP BY productID

) AS is

      INNER JOIN Products p ON (p.productID = is.productID)


The syntax is takes a while to get used to, but basically the subquery is the tricky part for most.

add comment
Feedback