I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.
I have two table variables:
DECLARE @Measurements TABLE(Precidence int, -- the relative scale of a measurement Measurement varchar(max), -- Grams, Kilograms, Liters, etc MeasurementType varchar(max)); -- Weight, Volume, etc Example: Precidence | Measurement | MeasurementType 2 | G | Weight 1 | KG | Weight 1 | GAL | Volume 2 | L | Volume 3 | ML | Volume DECLARE @Items TABLE(ItemType varchar(max), Quantity float, Measurement varchar(max), ToMeasurement varchar(max));
@Items table can contain multiple measurements for the same
ItemType. For each
ItemType I need to identify the largest measurement, taking into account incompatible measurement types and update the
ToMeasurement. The ultimate goal is to convert the
Quantity of each
ItemType to the largest measurement present in the
@Items table so items of the same
ItemType can be summed. I've already written conversion function and the sum operation.
Given the following table input:
ItemType | Quantity | Measurement | ToMeasurement Widget | 1 | G | NULL Widget | 1 | KG | NULL Widget | 1 | ML | NULL Widget | 1 | L | NULL
Should be updated to:
ItemType | Quantity | Measurement | ToMeasurement Widget | 1 | G | KG Widget | 1 | KG | KG Widget | 1 | ML | L Widget | 1 | L | L
I've rewritten the update multiple times and each time I come up short. At one point I had subqueries going four levels deep. I felt like I was getting close but it was getting so complex I couldn't see the forest for the trees. My most recent attempt is simpler but once again produces incorrect results:
UPDATE A SET A.ToMeasurement = E.Measurement FROM @Items A JOIN(SELECT C.ItemType, D.Measurement FROM @Measurements B JOIN @Items C ON C.Measurement = B.Measurement JOIN @Measurements D ON D.MeasurementType = B.MeasurementType)E ON E.ItemType = A.ItemType;
I know I'm not even using the
Precidence column, which is one of the reasons it is failing to produce the results I'm looking for.
Here's a query that produces the result I'm looking for (I think) but I'm still not sure how to turn it into an update statement:
SELECT A.ItemType, A.Quantity, A.Measurement, (SELECT TOP 1 M.Measurement FROM @Measurements M JOIN @Items C ON C.Measurement = M.Measurement WHERE M.MeasurementType = B.MeasurementType AND C.ItemType = A.ItemType ORDER BY Precidence)ToMeasurement FROM @Items A JOIN @Measurements B ON A.Measurement = B.Measurement;