Query Help - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query Help

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2007-03-12 : 05:06:56

I have 2 tables as follow

Category ahve lots of data for ex

PlanName category RName
Contract Loss PQR
Contract Pay DEF
Contract General ABC
Contract Gereral Test
Contract General XYZ

data

Key SeqNbr TKey RName
-------------- ---------- -------------- ------------------------------
10128 1 62746 PQR
10128 2 62747 DEF
10128 3 62748 TEST
10128 4 62749 XYZ
10291 1 63839 PQR
10291 2 63840 DEF
10291 3 63841 ABC
10291 4 63842 TEST

I want to update the sequence of TEST after DEF as "ABC" and "TEST" is
from same "Category" for Key 10291.
For 10128 no need to change the squernce because this key have only "TEST" from
category = "General"

my O/p should be

Key SeqNbr TKey RName
-------------- ---------- -------------- ----------------------------
10128 1 62746 PQR
10128 2 62747 DEF
10128 3 62748 TEST
10128 4 62749 XYZ
10291 1 63839 PQR
10291 2 63840 DEF
10291 3 63841 TEST
10291 4 63842 ABC

1.PLz tell me how I can find out key which have more than 1 RName from "General" Category and also

2.plz tell me how I can update

TEST SeqNbr would need to be changed to 3,
TKey to 63841 .ABC SeqNbr would have
to be changed to 4, TKey to 63842

I want to create a proc for this.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-12 : 08:03:35

Select Key, count(RNAME) as [No of RNAMEs)
from tbl t1 join category t2
on t1.pk = t2.pk
group by Key
having count(RNAME)>1

2. Why do you need procedure for this. Are you going to do this more than once. Also if seqnbr is key column, I suggest not to manipulate it for ordering purpose. Instead add some new column say, DisplayOrder, for this purpose.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources