All Forums Database
Lacsum 3 posts Joined 08/14
29 Aug 2014
Copy the record and increment in its one column

Hi, 
So i've been searching all around on how i could increment a column in Teradata. I'm new to this so im not really familiar with all the syntax or tactics that i could use. 
I've read about autogenerated identity column, OLAP and recursive in teradata but i think it does not matches my need. 
So here's my problem.
There are existing records in my table, so lets say i have 3 records ('a','b','c','d',1,'e')  && ('f','g','h','i',1,'j')  && ('k,'l','m','n',1,'o') 
then i need to replicate these records, and increment the only one integer column ('a','b','c','d',2,'e') && ('f','g','h','i',2,'j') &&  ('k,'l','m','n',2,'o')   ~
i need to repeat this until the max incrementation of integer (which is 15) is reached...
 
I have like miliion of records to update, and manual insertion would really help at this moment. 
Any advise would really help.
 
 

Lacsum 3 posts Joined 08/14
30 Aug 2014

So, i though of insert select and putting up the integer value as null....then update it via
SEL CSUM(1,1) + 1 AS SEQ
FROM SYS_CALENDAR.CALENDAR

QUALIFY SEQ < 16

 

However, i have to copy each record 15 times... and i have millions of it..

dnoeth 4628 posts Joined 11/04
30 Aug 2014

You are close :-)
Cross join to a list of numbers between 1 and 15:

insert into tab
select tab.col1,tab.col2,tab.colx + n 
from tab cross join 
 ( -- get numbers from 1 to 15
   select top 15 day_of_calendar as n
   from sys_calendar.calendar
   order by day_of_calendar
 ) as dt

 

Dieter

Lacsum 3 posts Joined 08/14
30 Aug 2014

Hi Dieter!!
 
Im a fan of yours, ive been reading most of your posts here and it really helped me a lot to improve my teradata sql skills.. 
by the way, i have tried it and it worked!! thank you and God! 
 

You must sign in to leave a comment.