Help with tricky update statement

Question

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));

The @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.

Notes

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;

Answer 1

The performance impact from multiple logical reads could be a result of your UDF.

Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.

http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx

To replicate your scenario, I executed the following query:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
             Measurement varchar(max), -- Grams, Kilograms, Liters, etc
             MeasurementType varchar(max)); -- Weight, Volume, etc`

DECLARE
   @Items TABLE(ItemType varchar(max),   
            Quantity float,  
            Measurement varchar(max),   
            ToMeasurement varchar(max));

insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L'); 

insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume'); 

Afterwards, I complied and executed the following update statement:

WITH Items_CTE AS
   (
        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
    )  
UPDATE @Items  
SET ToMeasurement = cte.ToMeasurement  
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;

The UPDATE statement uses a CTE and the results were as follows:

ItemType | Quantity | Measurement | ToMeasurement  
Widget   |    1     |    G        |    KG  
Widget   |    1     |    KG       |    KG  
Widget   |    1     |    ML       |    L  
Widget   |    1     |    L        |    L

I hope this information helps.

Thank you,

AMB

Answer 2

I still haven't been able to accomplish this as an UPDATE statement but I was able to figure out how do it while the data is being inserted into the table in the first place. The @Items table variable was being populated by a table valued function, @GetItems.

INSERT INTO @Items(ItemType,
                   Quantity,
                   Measurement)
SELECT *
FROM dbo.GetItems(@Invoice);

So I modified it to use the query I had created:

INSERT INTO @Items(ItemType, 
                 Quantity, 
                 Measurement, 
                 ToMeasurement)
SELECT A.ItemType, 
     A.Quantity, 
     A.Measurement, 
     (SELECT TOP 1 M.Measurement
      FROM @Measurements M
          JOIN dbo.GetItems(@Invoice)C
          ON C.Measurement = M.Measurement
      WHERE M.MeasurementType = B.MeasurementType
        AND C.ItemType = A.ItemType
      ORDER BY Precidence)ToMeasurement
FROM dbo.GetItems(@Invoice)A
    JOIN @Measurements B
    ON A.Measurement = B.Measurement;

This does result in the multiple read operations on the tables underlying the GetItems function but in this case it should be relatively small amount of data.

Still, it's not an ideal solution and I'm still interested in knowing how to do this in an UPDATE so feel free to post an answer if you figure it out.