DELETE TABLE_ITEM_SHELVES
GO
INSERT INTO TABLE_ITEM_SHELVES
SELECT ITEM1, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM1 IS NULL
UNION
SELECT ITEM2, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM2 IS NULL
UNION
SELECT ITEM3, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM3 IS NULL
UNION
SELECT ITEM4, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM4 IS NULL
GO
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM
(SELECT ITEM1, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM1 IS NULL
GROUP BY ITEM1) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM1
GO
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM
(SELECT ITEM2, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM2 IS NULL
GROUP BY ITEM2) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM2
GO
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM
(SELECT ITEM3, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM3 IS NULL
GROUP BY ITEM3) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM3
GO
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM
(SELECT ITEM4, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM4 IS NULL
GROUP BY ITEM4) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM4
GO
SELECT * FROM TABLE_ITEM_SHELVES
GO