patternMinor
Update row based on match to previous row
Viewed 0 times
previousupdatematchbasedrow
Problem
I have the following table:
The requirement I am battling with is to update the
Attempt:
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,1Attempt:
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 1Code 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 1Context
StackExchange Database Administrators Q#34243, answer score: 4
Revisions (0)
No revisions yet.