12,397,805 members (47,492 online)
Rate this:
See more:
```Seq_1	Seq_2     DPflag   MRP     seq	  ref
~~~~~   ~~~~~    ~~~~~~~   ~~~     ~~~   ~~~
2	2	  NA	   0	    1	  0
2	2	  A	   25	    2	  1
3	3	  2        5	    3	  2
4	4	  NA	   50	    4	  3
4	4	  A	   20	    5	  4
5	5	  A	   50	    6  	  5
1	1	  A	   220	    7	  6
```
I want to Update seq_2 increment by 1 When Dpflag='NA' and Greater then Seq_1
note There are two 'NA' IN DPflag so it must increment Two Times

Sorry for alignment,consider as a table
Posted 3-Apr-13 5:12am
Updated 3-Apr-13 5:21am
RedDk14.1K
v2
ThePhantomUpvoter 3-Apr-13 11:19am

Sounds like a job for a CASE statement.

Rate this:

## Solution 1

Test it:
```DECLARE @tmp TABLE(Seq_1 INT, Seq_2 INT, DPflag VARCHAR(3), MRP INT, seq INT, ref INT)

INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(2, 2, 'NA', 0, 1, 0)
INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(2, 2, 'A', 25, 2, 1)
INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(3, 3, '2', 5, 3, 2)
INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(4, 4, 'NA', 50, 4, 3)
INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(4, 4, 'A', 20, 5, 4)
INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(5, 5, 'A', 50, 6, 5)
INSERT INTO @tmp (Seq_1, Seq_2, DPflag, MRP, seq, ref)
VALUES(1, 1, 'A', 220, 7, 6)

SELECT *
FROM @tmp

UPDATE @tmp
SET t1.Seq_2 = t2.Seq_2
FROM @tmp AS t1 INNER JOIN (
SELECT Seq_1, Seq_2 + 1 AS Seq_2, DPflag, MRP, seq, ref
FROM @tmp
WHERE DPFlag='NA' AND Seq_1=Seq_2) AS t2 ON (t1.Seq_1 =t2.Seq_1 AND t1.DPFlag=t2.DPFlag)

SELECT *
FROM @tmp```

Top Experts
Last 24hrsThis month
 OriginalGriff 380 Richard Deeming 244 Richard MacCutchan 225 Karthik Bangalore 190 ppolymorphe 120
 OriginalGriff 6,368 Karthik Bangalore 2,572 ppolymorphe 2,455 F-ES Sitecore 1,977 Richard MacCutchan 1,952