updating the column - 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

General SQL Server Forums

New to SQL Server Programming

updating the column

Author

Topic

venkatv
Starting Member

4 Posts

Posted - 2009-09-24 : 21:50:48

Hi All,

Can any one help me out on my requirement.
I have a table "Phone" which has 3 columns
say

colA, colB, colC where colA, colB are associated as many-to-many.

Ex:

colA colB
54 19
54 15
56 20
58 20
58 18
58 16

what i need is based on the association i need to update the colC value as 1,2,3 soo on

that is:

colA colB colC
54 19 1
54 15 2

56 20 1

58 20 1
58 18 2
58 16 3

It would be a great favour if any one help me out on this.

--Venkat.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-25 : 00:57:48

update t
set colc = s.rid
from tablename t
inner join
(select row_number()over(partition by cola order by cola) as rid ,* from tablename)s
on s.cola = t.cola and s.colb = t.colb

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-25 : 01:04:23

HI try this once,

select id identity(int,1,1),* into #temp from Tablename

select *,(select count(*) from tablename where id<= t.id and t.cola = cola ) from tablename t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 03:19:21

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

venkatv
Starting Member

4 Posts

Posted - 2009-09-25 : 11:17:51

quote:
Originally posted by bklr

update t
set colc = s.rid
from tablename t
inner join
(select row_number()over(partition by cola order by cola) as rid ,* from tablename)s
on s.cola = t.cola and s.colb = t.colb

Thanks bklr for your very quick response,

I tried executing the query, but its failing at ROW_NUMBER, Its my fault that i didn't mention before that we are using MS SQL Server 2000. (where the database properties compatibility is set to 80).

do we have any other alternate solution for MS SQL Server 2000.

Regards,
Venkat.

venkatv
Starting Member

4 Posts

Posted - 2009-09-25 : 11:19:27

quote:
Originally posted by Nageswar9

HI try this once,

select id identity(int,1,1),* into #temp from Tablename

select *,(select count(*) from tablename where id<= t.id and t.cola = cola ) from tablename t

Hi Nageshwar,

Thanks for your reply, the identity is not working, as i am using MS SQL Server 2000, thats my fault i didnt mention before.

Please respond if we have any alternate solution with 2000.

Regards,
Venkat.

venkatv
Starting Member

4 Posts

Posted - 2009-09-25 : 11:20:17

quote:
Originally posted by madhivanan

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

Hi Madhavan,

Thanks for reply, I am using MS SQL Server 2000.

Regards,
Venkat.

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-25 : 11:28:42

What version of SQL are you using?

[ /fail at query]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-25 : 11:30:26

As far as I can see OP has already answered that question 3 times in this very thread..

quote:
Originally posted by winterh

What version of SQL are you using?

[ /fail at query]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 02:11:02

quote:
Originally posted by venkatv

quote:
Originally posted by madhivanan

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

Hi Madhavan,

Thanks for reply, I am using MS SQL Server 2000.

Regards,
Venkat.

Use method 1 http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources