HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Update row based on match to previous row

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
previousupdatematchbasedrow

Problem

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)

Solution

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

Code Snippets

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;
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

Context

StackExchange Database Administrators Q#34243, answer score: 4

Revisions (0)

No revisions yet.