Help need on an update query - 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)

Help need on an update query

Author

Topic

susanthosinha
Starting Member

1 Post

Posted - 2005-12-18 : 23:32:48

Hi,

I need to update a field in a sequential order.

In breif my requirement is to update a particular Field. The table consists of three Key fields. Deptid, Emplid, SeqNo.
I want to write an SQL to update the SEQNO. The SeqNo has numbers in random. I would like to see the numbers arranged in ascending order and giving them a value based on the order of incresing value.

eg

Deptid Emplid SeqNo Comments NewSeqNo(to be updated)
--------------------------------------------------------------------
1 d01 745 ----- 2nd greatest ----- 2
1 d01 789 ----- 3rd greatest ----- 3
1 d01 390 ----- 1st greatest ----- 1
1 d02 230 ----- 1st greatest ----- 1
1 d02 980 ----- 2nd greatest ----- 2

Database Before and After Update is to updated as mentioned below.

Before Update
Deptid Emplid SeqNo Name DOB
--------------------------------------------
1 d01 745 dfg 10/10/1980
1 d01 789 gdfg 10/11/1980
1 d01 390 gdfg 10/12/1980
1 d02 230 dg 10/13/1980
1 d02 980 gdfg 10/14/1980

After Update
Deptid Emplid SeqNo Name DOB
------------------------------------------
1 d01 2 dfg 10/10/1980
1 d01 3 gdfg 10/11/1980
1 d01 1 gdfg 10/12/1980
1 d02 1 dg 10/13/1980
1 d02 2 gdfg 10/14/1980

Please let me know if such a query is possible. Also feel free to get back to me in case you need any other clarification on the query above.
Any ideas or inputs will be of great help to me.

Thanks in advance.

Susantho Sinha

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-18 : 23:39:44

Hi Susantho,
as you said
"I would like to see the numbers arranged in ascending order and giving them a value based on the order of incresing value. "
but 2 rows have the same value "1" and "2" .
Are you sure if you want the same output as specified in after update. clarify what exactly are you looking for?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-18 : 23:45:07

What's your requirement for updating the seqno ? If you only need to retrieve the record back in a certain order, you can use 'order by'
for example :

select Deptid, Empid, SeqNo, Name, DOB
from yourtable
order by Deptid, Empid, SeqNo

Or for whatever reason that you need to update the SeqNo, you can create a temp table with an int identity column and insert into the temp table by selecting from yourtable with 'order by' in the order you required and then use the temp table to update back the seqno of yourtable.

-----------------
[KH]
Learn something new everyday

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-19 : 00:17:20

I think what you actually want to do is to update SeqNo to be the count of all records where SeqNo is lower, plus one.

Note that I called the table departmentdata because I didn't know what your table was named.

update departmentdata
set seqno = newseqno
from departmentdata x 
   left join (select d1.deptid, d1.emplid, d1.seqno, count(d2.seqno) + 1 as newseqno
              from departmentdata d1 
                   left join departmentdata d2
                   on d1.deptid = d2.deptid 
                   and d1.emplid = d2.emplid
                   and d1.seqno > d2.seqno
              group by d1.deptid, d1.emplid, d1.seqno) y
on x.deptid = y.deptid 
and x.emplid = y.emplid
and x.seqno = y.seqno

I strongly recommend however that you do not use SeqNo to store the NewSeqNo, because once you have updated the data, you cannot ever get back to the original information....ie keep the new value in a new field

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources