Update column with sort items

Question

Hello,

In a given relationship table I have a column that stores the ordering, but due to a problem in the application this column was populated wrong data, as the example

|user_log
-------------------------------------------
|user_id    |log_id |seq_num| created_date |
-------------------------------------------
|175300     |368501 |0      |
|175300     |368502 |0      |
|175300     |368503 |0      |
|175336     |368504 |0      |
|175389     |368505 |0      |
|175389     |368506 |0      |

I need to create an sql script, which corrects this ordering by populating the seq_num column with the order of creation, determined by the created_date column. Example:

-------------------------------------------
|user_id    |log_id |seq_num| created_date |
-------------------------------------------
|175300     |368501 |0      |
|175300     |368502 |1      |
|175300     |368503 |2      |

Does anyone have an idea of how to build this sql script? NOTE: the database is Oracle.

Answer 1

Hello,

For this it is necessary to run the following sql:

MERGE INTO user_log t2 using (select log_id, rownum n from user_log order by created_date) t1 on (t1.log_id = t2.log_id) WHEN MATCHED THEN UPDATE set t2.seq_num = t1.n

Changing the use of the log_id field by the Primary key.

Yeah, but what does it do?

first do the sub-select:

select log_id, rownum n from user_log order by created_date

selects the ID and the row number corresponding to the table sorted by date, the table looks like this:

___________________ | log_id | rownum | |--------|--------| | 5001 | 0 | | 5003 | 1 | | 5002 | 2 | | 5004 | 3 | -------------------

Then the table is scanned for a condition that satisfies the merge condition:

on (t1.log_id = t2.log_id)

if you have found any records update:

WHEN MATCHED THEN UPDATE set t2.seq_num = t1.n

I hope I’ve cleared up your doubts.

#EDIT

For the specific case of seq_num having to start from 0 for each user must use the following SQL:

merge into user_log t2 using ( select user_id, log_id, ROW_NUMBER() over (partition by user_id order by created_date) as seq_num from user_log ) t1 on (t1.log_id = t2.log_id and t1.user_id = t2.user_id) when matched then update set t2.seq_num = (t1.seq_num -1)

For more information about the command used visit the following link:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm