Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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 6:12am
Edited 3-Apr-13 6:21am
RedDk10K
v2
Comments
ThePhantomUpvoter at 3-Apr-13 11:19am
   
Sounds like a job for a CASE statement.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 475
1 Maciej Los 339
2 Richard MacCutchan 225
3 BillWoodruff 219
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,759
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,269
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 3 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100