Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
How do you know the previous row will be RowID - 1? If this is an identity column (and even if it isn't) you can never be sure about gaps. –  Aaron Bertrand Feb 6 '13 at 15:52
    
Hi, I used row_number in previous statement (didn't include that section apologies) –  Peter PitLock Feb 6 '13 at 15:53
add comment

2 Answers

up vote 4 down vote accepted
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
share|improve this answer
    
Brilliant! thanks! –  Peter PitLock Feb 6 '13 at 15:58
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.