Search code examples
sqladvantage-database-server

SQL count how many times a item is sold from invoice table

We lost our count of how many times a product have been sold since day one and I need to update the item table's Sold column. There is only one item per line in the invoiceItem table.

Items table:

ItemNumber Char(8)
Sold       Integer

InvoiceItem table

InvoiceNumber  Integer  
ItemNumber Char(8)

I know how to get the count of one item, but I don't know how to go through all rows of the InvoiceItem table.

Update Items set Sold=
(Select Count(ItemNumber) from InvoiceItem
where ItemNumber = 'B12456')
Where ItemNumber = 'B12456';

Thanks for any help. KimHJ

Solution

  • You should be able to use a correlated subquery:

    Update Items
    set Sold = (Select Count(*) from InvoiceItem ii where ii.ItemNumber = items.ItemNumber);