SQL: updating a column based on other columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL: updating a column based on other columns

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-10-18 : 17:46:09

I have a tableA: (ID int, matchPID char,PID char, set int, num char, QID char, QStatus char)

ID matchPID PID set num QID QStatus
1 00001230 xx123 1 234
2 00001229 xx234 1 214
3 00000054 xx654 1 NULL
4 00012000 xx125 2
5 00A53214 xx321 2
6 00000100 xx213 2
matchPID is always (00-xxxxxx) x can be char or int.

now I have to populate OID just one value for each set according to [num].

If for any set, there exists any value in [num], then populate OID with PID where exists [num] for same set and populate Qstatus = 'fail' in rest.
so OID can be populated in id 1 or 2. (any1), then check lowest matchPID and populate here. so in this case (set1) expected result is :

ID matchPID PID set num QID QStatus

1 00001230 xx123 1 234 NULL FAIL
2 00001229 xx234 1 214 xx234 NULL
3 00000054 xx654 1 NULL NULL FAIL
if for any set there isnt any [num] found, then pick lowest matchPID and populate QID with PID and fail the rest.
notice that matchPID starts with 00 and then it can be 0>1>2>..>A>B>C>.... so expected result for set2 is :

ID matchPID PID set num QID QStatus

4 00012000 xx125 2 NULL NULL FAIL
5 00A53214 xx321 2 NULL NULL FAIL
6 00000100 xx213 2 NULL xx213 NULL
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 04:22:56

what do you refer to as a set here? can you explain how you got those values for QID QStatus ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-19 : 05:13:38

First we need to see the formatted post:
(you can place [C0DE] [/C0DE] around)


I have a tableA: (ID int, matchPID char,PID char, set int, num char, QID char, QStatus char)

ID matchPID   PID    set  num   QID  QStatus 
1  00001230   xx123   1   234  
2  00001229   xx234   1   214
3  00000054   xx654   1   NULL
4  00012000   xx125   2   
5  00A53214   xx321   2   
6  00000100   xx213   2
matchPID is always (00-xxxxxx) x can be char or int.

now I have to populate OID just one value for each set according to [num].

If for any set, there exists any value in [num], then populate OID with PID where exists [num] for same set and populate Qstatus = 'fail' in rest.
so OID can be populated in id 1 or 2. (any1), then check lowest matchPID and populate here. so in this case (set1) expected result is :

ID matchPID   PID    set  num   QID       QStatus 

1  00001230   xx123   1   234   NULL       FAIL
2  00001229   xx234   1   214   xx234      NULL
3  00000054   xx654   1   NULL  NULL       FAIL
if for any set there isnt any [num] found, then pick lowest matchPID and populate QID with PID and fail the rest.
notice that matchPID starts with 00 and then it can be 0>1>2>..>A>B>C>.... so expected result for set2 is :

ID matchPID   PID    set  num   QID       QStatus

4  00012000   xx125   2   NULL  NULL      FAIL
5  00A53214   xx321   2   NULL  NULL      FAIL
6  00000100   xx213   2   NULL  xx213     NULL
Thanks

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources