Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL update question

Posted on 2007-11-26
4
Medium Priority
?
241 Views
Last Modified: 2010-04-21
I have a table with 2 columns. Column1 is given and sorted ascending, what i need to do is update column2 with corresponding values.  Column 2 just should increment by one and reset to one when the value in column1 changes.



Col1      Col2
1      1
1      2
1      3
1      4
1      5
2      1
2      2
2      3
2      4
3      1
3      2
3      3
3      4
3      5
3      6
3      7
3      8
3      9
3      10


Thanks
sopheak
0
Comment
Question by:sopheak
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20353479
Here goes:

Hope this helps ...

-- creating original situation
create table #Y(Col1 int,Col2 int)
go
 
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(2, NULL)
insert into #Y values(2, NULL)
insert into #Y values(2, NULL)
insert into #Y values(2, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
go
 
-- actual code starts here
alter table #Y
add col3 int identity(1,1)
go
 
declare @c1 int
declare @c1p int
declare @c3 int
declare @c2 int
 
declare Yveau cursor
for
select col1, col3
from  #Y
 
open yveau
fetch yveau
into @c1, @c3
 
while @@fetch_status = 0
begin
        if isnull(@c1p,-1) != @c1
        begin
                set @c2 = 1
        end
        else
        begin
                set @c2 = @c2 + 1
        end
 
        update #y
        set    col2 = @c2
        where  col3 = @c3
 
        set @c1p = @c1
 
        fetch yveau
        into @c1, @c3
end
 
close yveau
deallocate yveau
 
alter table #Y
drop column col3
 
select * from #Y
 
-->> result:
Col1        Col2
----------- -----------
1           1
1           2
1           3
1           4
1           5
2           1
2           2
2           3
2           4
3           1
3           2
3           3
3           4
3           5
3           6
3           7
3           8
3           9
3           10
 
--

Open in new window

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20353492
To be completely perfect, the cursor should have been declared like this:

Hope this helps ...

declare Yveau cursor
for
select col1, col3 
from #Y 
order by col3 asc
 
--

Open in new window

0
 
LVL 35

Accepted Solution

by:
David Todd earned 720 total points
ID: 20354290
Hi,

Avoid cursors like the plauge ...

If you are using SQL 2005, look at the row_number() function.

If you are using SQl 2000 or earlier, then I'd use a temp table with an identity to uniquely identify those rows and then build an update statement, rather than using a loop or cursor.

HTH
  David
use tempdb
go
 
 
if object_id( N'tempdb..#Y', N'U' ) is not null 
	drop table #Y;
	
create table #Y(Col1 int,Col2 int)
go
 
set nocount on
 
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(1, NULL)
insert into #Y values(2, NULL)
insert into #Y values(2, NULL)
insert into #Y values(2, NULL)
insert into #Y values(2, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
insert into #Y values(3, NULL)
 
set nocount off
 
select *
from #Y
go
 
select 
	row_number() over( partition by col1 order by col1 ) as RowNumber
	, col1
from #Y
go
 
update y
	set y.col2 = y.RowNumber
from 
	(
	select 
		row_number() over( partition by col1 order by col1 ) as RowNumber
		, col1
		, col2
	from #Y	
	) y
 
select *
from #Y
go

Open in new window

0
 

Author Closing Comment

by:sopheak
ID: 31411075
Thanks for the row_number
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Screencast - Getting to Know the Pipeline
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question