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.