All Forums General
Jegan_la 8 posts Joined 08/14
07 Aug 2014
Query to update the sequence number of duplicate rows

Hi All,
I am new to teradata and this is my first post.
I would like to know the possibility of generating a query with which can order the duplicate row in sequence.
 
for ex:
field 1    field 2    field 3    field 4     count
aaa        bbb        cccc      dddd          1
aaa        bbb        cccc      dddd          2
aaa        eee        ffff        gggg          1
 
Thanks
 

dnoeth 4628 posts Joined 11/04
07 Aug 2014

You can get the count using a

ROW_NUMBER() OVER (PARTITION BY field1, field,2, field3, field4 ORDER BY 1)

But there's no way to use this in an UPDATE.
You could INSERT SELECT those rows with the count in another table, then DELETE the rows in the original table and INSERT SELECT back from the 2nd table.
 

Dieter

Jegan_la 8 posts Joined 08/14
07 Aug 2014

Thank you very much Dieter, it works exactly as i expected.

You must sign in to leave a comment.