Query to update the sequence number of duplicate rows

General
Enthusiast

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

2 REPLIES
Junior Contributor

Re: Query to update the sequence number of duplicate rows

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.

Enthusiast

Re: Query to update the sequence number of duplicate rows

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