Sql how to add identity column with condition?
I am working on ms SQL server Actually my requirement is I need to add a new identity field ID to an existing table with a condition which should be like in the table below the table is:
Col1 ID
1 1
1 2
1 3
0 Null
1 4
0 Null
Rony cortze
First, add the new column:
alter table t add column id int;
Note: id is a really bad name for a column that can be null. Then:
with toupdate as (
select t.*,
row_number() over (partition by col1 order by ) as seqnum
from t
)
update toupdate
set id = (case when col1 = 1 then serum end);
Strictly speaking, you don’t need to update the values when col1 = 0, because the default value is NULL. However, in case you want a different value there, I am leaving out where col1 = 1.
Dominic
Your query should be like below :
update
update