Update row based on match to previous row

Question

I have the following table:

Declare @m_TestTable table
(
RowID                  int,
OrderNo                nvarchar(10),
DetailID               int,
FirstUniqueDetailEntry int
)

Insert into @m_TestTable Values (1,'Order1',1,1)
Insert into @m_TestTable Values (2,'Order1',1,1)
Insert into @m_TestTable Values (3,'Order2',10,1)
Insert into @m_TestTable Values (4,'Order2',10,1)
Insert into @m_TestTable Values (5,'Order2',15,1)
Insert into @m_TestTable Values (6,'Order3',20,1)
Insert into @m_TestTable Values (7,'Order3',20,1)
Insert into @m_TestTable Values (8,'Order3',30,1)

The requirement I am battling with is to update the FirstUniqueDetailEntry column to be 0 if the OrderNo and DetailID are the same as the previous OrderNo and DetailID, resulting in:

'Order1',1,1
'Order1',1,0
'Order2',10,1
'Order2',10,0
'Order2',15,1
'Order3',20,1
'Order3',20,0
'Order3',30,1

Attempt:

Update @m_TestTable
Set FirstUniqueDetailEntry  = 0 
where  
    [OrderNo] = (Select [OrderNo] from @m_TestTable t where t.RowID =  RowID -1)
and [DetailID] = (Select [DetailID] from @m_TestTable t where t.RowID =  RowID -1)

Answer 1

UPDATE m 
  SET FirstUniqueDetailEntry = CASE 
    WHEN m2.OrderNo = m.OrderNo
    AND m2.DetailID = m.DetailID THEN 0 ELSE 1 END
  FROM @m_TestTable AS m 
  INNER JOIN @m_TestTable AS m2
  ON m.RowID = m2.RowID + 1;

SELECT * FROM @m_TestTable ORDER BY RowID;

Results:

RowID   OrderNo   DetailID   FirstUniqueDetailEntry
-----   -------   --------   ----------------------
1       Order1    1          1
2       Order1    1          0
3       Order2    10         1
4       Order2    10         0
5       Order2    15         1
6       Order3    20         1
7       Order3    20         0
8       Order3    30         1

Answer 2

You can can self join to the table and leverage the RowID(replace FooBar with your table variable):

UPDATE  a
SET FirstUniqueDetailEntry = 0
FROM FooBar a 
  JOIN FooBar b ON (a.OrderNo=b.OrderNo AND a.DetailID=b.DetailID)
WHERE a.RowID > b.RowID

SELECT * FROM FooBar ORDER BY OrderNo,DetailID

Results:

| ROWID | ORDERNO | DETAILID | FIRSTUNIQUEDETAILENTRY |
-------------------------------------------------------
|     1 |  Order1 |        1 |                      1 |
|     2 |  Order1 |        1 |                      0 |
|     3 |  Order2 |       10 |                      1 |
|     4 |  Order2 |       10 |                      0 |
|     5 |  Order2 |       15 |                      1 |
|     6 |  Order3 |       20 |                      1 |
|     7 |  Order3 |       20 |                      0 |
|     8 |  Order3 |       30 |                      1 |

Example at SQL Fiddle.