Solved

SQL update question

Posted on 2007-11-26
4
190 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the row_number
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
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…

771 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

15 Experts available now in Live!

Get 1:1 Help Now