adding a new column

[chamilz]

Hi...my reference table looks like this
id prod_id color_code
1 100 20
2 100 30
3 105 20
4 105 30
5 105 40
6 120 20

i need to create a table assigning coulor counter as follow
id prod_id color_code Countr
1 100 20 1
2 100 30 2
3 105 20 1
4 105 30 2
5 105 40 3
6 120 20 1

how can i do this...thanks in advance

 

[stevexff]

You can add a column with ALTER TABLE. But it must be either nullable, or have a default value, or it can't be added.

After this, you can update the values that aren't defaults or nulls, and ALTER the table again to remove the default value or the nullability.

 

[miq]

hi,

alter table [reference]

add column Countr int not null
go

B.R,
miq

 

[chamilz]

Thanks for your reply...

i better explain my requirement in detail...

in the result table, column counter represent the number of color code for the perticular product. Above, for the product code 100 has 2 color code and counter/rank should show 1 and 2. when it comes to next product ie.105 my counter should be 1,2,3.