Friday 21 June 2013

Adding sequential values to identical rows

I was recently presented with a requirement whereby I had a list of orders, with parts, and a list of orders with serial numbers with which I needed to update the original table.

Let's look at the OrderedParts table, which contains a list of the parts used on a customer orders:

OrderNo PartNo SerialNo
1234 ABC1
1234 ABC1
1252 XYZ3
1252 HHJ3

This seems straightforward for order number 1252 as the parts are unique, but consider that the orders could be for multiple of the parts, and the only way of telling rows apart is with the serial number, as in order 1234.

Here is our table of available parts with serial numbers:

PartNo SerialNo
ABC1 X112
ABC1 X113
XYZ3 I330
HHJ3 K283

We will want the OrderParts table to be updated such that the serial numbers X112 and X113 each appear once in it, showing those serial numbers are used in the order.

This presents us with a problem in the traditional update statement; how do we join these tables together such that we can update the records correctly?

Well, let us consider the an update statement:


/* Code snippet coded by Dave Green @d_a_green - June 2012*/
/* Set up tables*/

DECLARE @OrderedParts TABLE
    
(
      
OrderNo INT NOT NULL ,
      
PartNo VARCHAR(100) NOT NULL ,
      
SerialNo VARCHAR(100) NULL
    )
INSERT  @OrderedParts

VALUES  ( 1234, 'ABC1', NULL )
,       (
1234, 'ABC1', NULL )
,       (
1252, 'XYZ3', NULL )
,       (
1252, 'HHJ3', NULL )


DECLARE @Stock TABLE
    
(
      
PartNo VARCHAR(100) NOT NULL ,
      
SerialNo VARCHAR(100) NOT NULL
    )

INSERT  @Stock
        
( PartNo, SerialNo )

VALUES  ( 'ABC1', 'X112' )
,       (
'ABC1', 'X113' )
,       (
'XYZ3', 'I330' )
,       (
'HHJ3', 'K283' )

--Try joining the tables on the part number
UPDATE  @OrderedParts

SET     SerialNo = Stock.SerialNo
FROM    @OrderedParts
        
INNER JOIN @Stock Stock 

        ON [@OrderedParts].PartNo = Stock.PartNo


SELECT  
FROM    @OrderedParts

That produces the result:

OrderNo PartNo SerialNo
1234 ABC1 X112
1234 ABC1 X112
1252 XYZ3 I330
1252 HHJ3 K283

Which, as you can see has done the instances where only one instance of each part was used in the order (order 1252) , but has not covered instances where multiple of the same part was used in the order (order 1234).

So how can we get round this?

Well, I chose to think about them in terms of "we want the first matching part in the first row, and the second matching part in the second row". This got me thinking about how we arrange the rows - numerically, and about using the ROW_NUMBER() function.

So, we can easily add a row number by using something like:


SELECT  *,ROW_NUMBER() OVER (ORDER BY PartNo)

FROM    @OrderedParts


So - we need to give both tables a row number, and then use this as part of our join criteria for the update:

UPDATE  OrderedParts

SET     SerialNo = Stock.SerialNo 

FROM    ( SELECT    *,
                    
ROW_NUMBER() OVER ( ORDER BY PartNo ) AS RowNumber
          
FROM      @OrderedParts
        
) AS OrderedParts

INNER JOIN ( SELECT *,
                  
ROW_NUMBER() OVER ( ORDER BY PartNo ) AS RowNumber
           
FROM   @Stock
         
) AS Stock ON OrderedParts.PartNo = Stock.PartNo
                       
AND OrderedParts.RowNumber = Stock.RowNumber



Using the row number as a joining criteria in this way gives the answer:

OrderNo PartNo SerialNo
1234 ABC1 X112
1234 ABC1 X113
1252 XYZ3 I330
1252 HHJ3 K283

This has achieved our desired result, of each available serial number being used uniquely within the order.