DELETE FROM QuantityDiscount
WHERE ID IN (
	SELECT DISTINCT I.QuantityDiscountID
	FROM Item AS I
	LEFT JOIN QuantityDiscount AS QD ON (I.QuantityDiscountID = QD.ID)
	LEFT JOIN SupplierList AS SL ON (I.ID = SL.ItemID)
	LEFT JOIN Supplier AS S ON (SL.SupplierID = S.ID)
	WHERE (I.SubDescription1 = '1') OR (I.SubDescription1 = '4')
)

GO

IF NOT EXISTS ((SELECT * FROM dbo.syscolumns WHERE ([name] = 'ITEM_ID') AND (id = (SELECT id FROM dbo.sysobjects WHERE (id = object_id(N'[dbo].[QuantityDiscount]')) AND (OBJECTPROPERTY(id, N'IsUserTable') = 1)))))
	ALTER TABLE [dbo].[QuantityDiscount] ADD
			[ITEM_ID] [int]

GO

INSERT INTO QuantityDiscount (ITEM_ID, Quantity1, Price1, Price1B) 
(
	SELECT I.ID, SL.MasterPackQuantity, Round(((I.Price) * 0.95), 2), Round(((I.PriceB) * 0.92), 2)
	FROM Item AS I
	LEFT JOIN QuantityDiscount AS QD ON (I.QuantityDiscountID = QD.ID)
	LEFT JOIN SupplierList AS SL ON (I.ID = SL.ItemID)
	LEFT JOIN Supplier AS S ON (SL.SupplierID = S.ID)
	WHERE ((((I.SubDescription1 = '1') OR (I.SubDescription1 = '4')) AND (SL.MasterPackQuantity is not null)) AND (I.SubDescription3 = '1'))
)

GO

UPDATE Item SET
	QuantityDiscountID = Q.QD_ID
FROM (
	SELECT ID AS QD_ID, ITEM_ID
	FROM QuantityDiscount
) AS Q
WHERE Item.ID = Q.ITEM_ID


GO

UPDATE Item SET QuantityDiscountID = 0 WHERE ID not in (SELECT ITEM_ID FROM QuantityDiscount)
