Sql-server – Help with tricky update statement

sql serversql-server-2005t-sqlupdate

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;

Best Answer

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
Related Question