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.
| Author |
Topic |
|
roshana
Starting Member
31 Posts |
 Posted - 2009-10-14 : 06:09:21
|
| Hi All,I have one table with the structureEmp id ,Contact no ,Department IDI have records in this table for Emp Id and Contact no fields (1000 rows)I have to update the value of Department from 1 to 16 in a sequential order egEmp1,014-89899256,1Emp2,014-89899786,2..........Emp16,014-89899786,16Emp17,014-89899745,1Emp18,014-89899744,2Without using cursor can we update the value in Department ID fieldThanksRoshan |
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-10-14 : 06:29:25
|
| Update <Table_name> Set DepartmentId=SrNoFrom ( Select EmpId, row_number() Over(order by empid) SrNo From <Table_name> )AWhere <Table_name>.EmpId=A.EmpId And A.SrNo<=16 |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-14 : 07:10:12
|
[code]update t1set Department_ID=dt.num16from your_table t1join ( select case rn%16 when 0 then 16 else rn%16 end as num16, Emp_id from ( select row_number() over (order by Emp_id) as rn, Emp_id from your_table )dt1)dton dt.Emp_id = t1.Emp_id[/code]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-14 : 08:36:29
|
Cool and quirky!Added to my favorites...Great madhi 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-14 : 08:43:59
|
quote:
Originally posted by webfred
Cool and quirky!Added to my favorites...Great madhi
No, you're never too old to Yak'n'Roll if you're too young to die.
Thanks MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
update values with out using cursor