CODEWITHSUNDEEP
sqlteradata
hard coder
hard coder

Reputation: 5715

How to update a column's value using row number in Teradata

I want to update a column's value in this way

new value  = old value + row_number() * 1000

also for row_number I want to use order by old value

but I didn't find any solution.

sample data

column    
   1
   3
   5

after update query it should be

column
  1001
  2003
  3005

Upvotes: 0

Views: 2353

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

CREATE VOLATILE TABLE test, NO FALLBACK
(MyCol SMALLINT NOT NULL)
PRIMARY INDEX (MyCol)
ON COMMIT PRESERVE ROWS;

INSERT INTO test VALUES (1);
INSERT INTO test VALUES (3);
INSERT INTO test VALUES (5);

SELECT MyCol FROM test;

UPDATE test
  FROM (SELECT MyCol
             , ROW_NUMBER() OVER (ORDER BY MyCol) AS RowNum_
          FROM test) DT1
   SET MyCol = test.MyCol + (RowNum * 1000)
 WHERE test.MyCol DT1.MyCol;

SELECT MyCol FROM TEST;

Upvotes: 3

Related Questions