Solved

SQL update question

Posted on 2007-11-26
4
197 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 180 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Set bit value based on sums 4 47
Passing value to a stored procedure 8 89
How can I get this column in my query? 2 39
Need help debbuging stored procedure 21 32
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A short film showing how OnPage and Connectwise integration works.

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now