Help with tricky update statement

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 c

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://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

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

StackExchange Database Administrators Q#35919, answer score: 3