CODEWITHSUNDEEP
sqlsql-server
loki
loki

Reputation: 2966

How to update my column Value=Value+1 ?

I would like to update test1 table some rows. ColumnB must be 10 11,12,13,14 .... But My table

For example:

ColumnA is parent node. ColumnA+ColumnB is primary Key. Forexample ColumnA is CustomerNumber. ColumnB is Customer' ordernumber.

create table test1(ColumnA int,  ColumnB int,  ColumnC int);
Insert Into test1 Values(1, 9,123);
Insert Into test1 Values(1, 10,0);
Insert Into test1 Values(1, 10,0);
Insert Into test1 Values(1, 10,0);
Insert Into test1 Values(1, 10,0);
Insert Into test1 Values(2, 12,128);
Insert Into test1 Values(2, 12,0);
Insert Into test1 Values(3, 11,145);
Insert Into test1 Values(3, 11,0);

it should not that:

enter image description here

it should be that below:

enter image description here

My not working code: 


    update a set a.ColumnB=a.ColumnB+1
from test1 a 
inner join test1 b
on b.ColumnA=a.ColumnA
and a.ColumnC=0;
select * from test1;

How can I do that? How to update my first table to second one.

Upvotes: 0

Views: 488

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Try this:

WITH CTE
AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY ColumnA
                      ORDER BY ColumnB) AS RN
  FROM test1
)
UPDATE t 
SET t.ColumnB = CASE 
                  WHEN RN = 1 THEN ColumnB 
                  ELSE ColumnB + RN - 1 
                END  
FROM CTE AS t;

SQL Fiddle Demo

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try:

with T as(
select 
    row_number() over (partition by ColumnA order by ColumnA) Rnum, 
    min(ColumnB) over (partition by ColumnA) MinVal, 
    * 
From test1
) update T set ColumnB=MinVal+(Rnum-1)

Upvotes: 3

Related Questions