Delete records and update quantity

Question

We have an inventory management system which consists of an Item Catalog, Inventory, and Assets. Currently, we have an entry for every piece of inventory but we are now implementing a quantity on both the Inventory table and Assets table. For instance, data in the Inventory table looks something like this:

 InventoryID | ItemID
----------------------
 100         | 5
 101         | 5
 102         | 5
 103         | 5
 104         | 9
 105         | 5

What we now want to do is to merge the records with the same ItemID and put the Quantity in the field:

 InventoryID | ItemID | Quantity
---------------------------------
 100         | 5      | 5
 104         | 9      | 1

I have thousands of records that need merging and would like to know of a faster way to do this instead of the current way, which is finding the records, getting the count, deleting all but the latest record and updating the quantity field with the count (all being done manually in SSMS, not through any scripts).

Any help/suggestions would be appreciated.

The question is tagged SQL and the posted code is HTML tables? I'd suggest writing a SQL script to handle this merging. — Steven Mays, Nov 23 '15 at 20:24
Sorry, it was the only way I knew how to get the tables to display the data somewhat properly. If you tell me how or show me where I can learn to do it, I'd be glad to change it. — Robert, Nov 23 '15 at 20:28

score 2 · Accepted Answer · answered Nov 23 '15 at 20:37

2

Make a temp table and insert:

SELECT MIN(InventoryID), ItemID, COUNT(*) as Quantity
FROM Inventory
INTO #TEMP
GROUP BY ItemID

Then update the main table (create a quantity column first if you haven't):

UPDATE I
SET I.Quantity = T.Quantity
FROM #TEMP
WHERE I.InventoryID = T.InventoryID and I.ItemID = T.ItemID

Then delete the extra record from Inventory

DELETE
FROM INVENTORY
WHERE InventoryID not in(
   SELECT InventoryID
   FROM #TEMP)

answered Nov 23 '15 at 20:37

Aaron Dietz

10,137
1
14
26

Thanks for this.....I was able to use this in conjunction with some of my other scripts to get it working. – Robert Dec 02 '15 at 12:48

Chizzle · Answer 2 · 2015-11-23T20:40:33.360

0

Assuming you have a quantity field on your inventory table, you can update that field then delete the now-unnecessary records.

UPDATE Inventory
SET Inventory.Quantity = Computed.QCount
FROM Inventory
INNER JOIN 
(
    SELECT InventoryId, COUNT(*) as QCount
        FROM Inventory
    GROUP BY InventoryId
) as Computed
on Inventory.ItemId = Computed.ItemId

--Now Delete Duplicates

DELETE Inventory 
FROM Inventory
LEFT OUTER JOIN (
   SELECT MIN(InventoryId) as RowId, ItemId
   FROM Inventory 
   GROUP BY ItemId
) as KeepRows ON
   Inventory.InventoryId = KeepRows.RowId
WHERE
   KeepRows.ItemId IS NULL

edited Nov 23 '15 at 20:40

answered Nov 23 '15 at 20:28

Chizzle

1,707
1
17
26

True, but is there a way to get the quantity, delete all but 1 record for that ItemID, then update that record with the quantity? – Robert Nov 23 '15 at 20:29
Yes there is multiple ways of doing that. You could add the quantity column, then update it using the query above for ALL records, then you could delete duplicates based on which information you want to use as unique identifiers (hint: not the inventoryId). Here is the duplicate query I use: http://stackoverflow.com/a/18949/2868359 – Chizzle Nov 23 '15 at 20:33

score 0 · Answer 3 · answered Nov 23 '15 at 20:34

A simple script can create the new table that you want, then wipe out your old table and replace the data with the new.

For example, something like

SELECT 
   MIN(InventoryID) AS InventoryID,
   ItemID,
   COUNT(*) AS Quantity
INTO
   NewInventoryTable
FROM
   Inventory
GROUP BY 
   ItemID

Delete records and update quantity

3 Answers3