Refresh column with item sorting

Question

Refresh column with item sorting

Navigation

#1 by (2 votes)

2

Hello,

In a given relationship table I have a column that holds the sort, however due to a problem in the application this column was populated with 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 a sql script, which corrects this sort order by populating the seq_num column with the creation order, 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 how to mount this sql script? NOTE: The database is Oracle.

sql oracle

asked by anonymous 22.07.2016 / 19:08

1 answer

Laravel - Creating select with Form :: select + Model :: lists How do I store passwords in environment variables (and remove them from version control)?

score 2 · Accepted Answer

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.

Okay, but what does it do?

first do the sub-select:

select log_id, rownum n from user_log order by created_date

select the ID and row number for the table sorted by date, the table looks something like this:

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

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

on (t1.log_id = t2.log_id)

If you have found any registry make an update:

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

I hope to have clarified your doubts.

#EDIT

For the specific case of seq_num having to start from 0 for each user, 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 on the command used, visit the following link:

link