Hi guys--Query without cursors

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 2005 Forums

Transact-SQL (2005)

Hi guys--Query without cursors

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-29 : 07:01:31

Hi Friends

I have a table like this

id variables
----------- ----------
NULL a
NULL a
NULL a
NULL a
NULL b
NULL b
NULL b
NULL b
NULL c
NULL c
NULL c
NULL c

I need to update the id field so that it assigns number 1 for a, number 2 for b and number 3 for c.

I want the query without cursors.

Vic

Kristen
Test

22859 Posts

Posted - 2007-05-29 : 07:11:01

Change the ID column to have the IDENTITY attribute?

Kristen

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 07:26:12

Update <your table> set
id = Case <column has null>
when 'a' then 1
when 'b' then 2
when 'c' then 3 end

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-29 : 07:29:00

quote:
Originally posted by Kristen

Change the ID column to have the IDENTITY attribute?

Kristen

i think it will not work, coz o/p will be

id variables
1 a
2 a
3 a
4 a
5 b
....

likewise. n as per the req, he needs the o/p as:

id variables
1 a
1 a
1 a
1 a
2 b
....
3 c

thanks,

Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-29 : 07:32:44

quote:
Originally posted by svicky9

Hi Friends

I have a table like this

id variables
----------- ----------
NULL a
NULL a
NULL a
NULL a
NULL b
NULL b
NULL b
NULL b
NULL c
NULL c
NULL c
NULL c

I need to update the id field so that it assigns number 1 for a, number 2 for b and number 3 for c.

I want the query without cursors.

Vic

try out this:

create table #Temp
(ids numeric,
variables varchar(2))
go

insert into #temp (variables) values ('a')
insert into #temp (variables) values ('a')
insert into #temp (variables) values ('a')
insert into #temp (variables) values ('a')

insert into #temp (variables) values ('b')
insert into #temp (variables) values ('b')
insert into #temp (variables) values ('b')
insert into #temp (variables) values ('b')

insert into #temp (variables) values ('c')
insert into #temp (variables) values ('c')
insert into #temp (variables) values ('c')
insert into #temp (variables) values ('c')
go

update #temp set ids = (case when variables = 'a' then 1
when variables = 'b' then 2
when variables = 'c' then 3 end)
go

drop table #temp
go

thanks,

Mahesh

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-29 : 08:30:46

what if i have some 5 million distinct records in the variables columns???

Vic

http://vicdba.blogspot.com

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 08:37:21

try this...
declare @t table (id int, var1 char(1))
insert @t
select NULL, 'a' union all
select NULL, 'a' union all
select NULL, 'a' union all
select NULL,'a' union all
select NULL, 'b' union all
select NULL, 'b' union all
select NULL, 'b' union all
select NULL, 'b' union all
select NULL, 'c' union all
select NULL, 'c' union all
select NULL, 'c' union all
select NULL, 'c'

declare @t1 table(id int identity(1,1), variable char(1))
insert @t1
Select distinct var1 from @t

Update a set
id = b.id
from @t a join @t1 b on a.var1 = b.variable

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-05-29 : 09:09:51

Can three queries do instead of one? If the answer is yes, then try:
update <your_table> set Id = 1 where variables = 'a'
update <your_table> set Id = 2 where variables = 'b'
update <your_table> set Id = 3 where variables = 'c'

And might I ask why you do not want cursors?

Being a genius has its advantages...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 09:54:02

quote:
And might I ask why you do not want cursors?

And might I ask why you NEED cursors, especially in this case?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-29 : 10:03:40

thx pbguy

Vic

http://vicdba.blogspot.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-29 : 10:14:11

quote:
Originally posted by pbguy

try this...
declare @t table (id int, var1 char(1))
insert @t
select NULL, 'a' union all
select NULL, 'a' union all
select NULL, 'a' union all
select NULL,'a' union all
select NULL, 'b' union all
select NULL, 'b' union all
select NULL, 'b' union all
select NULL, 'b' union all
select NULL, 'c' union all
select NULL, 'c' union all
select NULL, 'c' union all
select NULL, 'c'

declare @t1 table(id int identity(1,1), variable char(1))
insert @t1
Select distinct var1 from @t

Update a set
id = b.id
from @t a join @t1 b on a.var1 = b.variable

Very nice!

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-29 : 18:11:57

Just for grins, here are 2 (very similar) ways do do it in 2005:

-- Version 1
UPDATE #temp
SET ids = Num
FROM
	(
		SELECT 
			variables,
			ROW_NUMBER() OVER (ORDER BY variables) AS Num
		FROM #temp
		GROUP BY 
			Variables
	) t
WHERE #temp.variables = t.variables

-- Version 2
UPDATE temp
SET ids = Num
FROM
	#temp temp
	INNER JOIN
	(
		SELECT 
			variables,
			ROW_NUMBER() OVER (ORDER BY variables) AS Num
		FROM #temp
		GROUP BY 
			Variables
	) t
	ON temp.variables = t.variables

-Ryan

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-05-30 : 02:41:56

quote:
Originally posted by harsh_athalye

quote:
And might I ask why you do not want cursors?

And might I ask why you NEED cursors, especially in this case?

Maybe because this is a problem of repetitive nature. Any suggestions on how to solve it?

Being a genius has its advantages...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources