13,097,012 members (81,612 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
RedDk17.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```

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Graeme_Grant 190 OriginalGriff 118 Jochen Arndt 110 Richard MacCutchan 70 Suvendu Shekhar Giri 58
 OriginalGriff 4,174 Graeme_Grant 2,412 ProgramFOX 2,067 Jochen Arndt 1,835 ppolymorphe 1,755

Advertise | Privacy |
Web02 | 2.8.170813.1 | Last Updated 3 Apr 2013