I am looking for a SQL solution to reorder records by updating order column.
Consider following data structure:
------------------------------------
| id | type | value | order | user |
------------------------------------
| 1 | A | 123 | 1 | 1 |
| 2 | B | 231 | 2 | 1 |
| 3 | B | 213 | 3 | 2 |
| 4 | A | 222 | 4 | 2 |
| 5 | A | 22 | 5 | 3 |
| 6 | B | 11 | 6 | 4 |
| 7 | C | 99 | 7 | 1 |
------------------------------------
Software(non-modifiable) that uses this data, takes order in account and applies value with max order for each user. However I need that type A records are applied first (have max order for particular users entries). Order column is unique.
Current solution is to create temporary table, selecting data in desired order and adding new identify column. Effectively rebuilding whole order column. Like this:
BEGIN TRANSACTION;
-- first we take type C
SELECT id
,type
,value
,[ORDER]
,user
INTO #tmp
FROM records
WHERE type = C;
-- we add new identify column
ALTER TABLE #tmp ADD new_order INT identify (1,1);
INSERT INTO #tmp
SELECT id
,type
,value
,[ORDER]
,user
FROM records
WHERE type = B;
-- last we take type A, these will have largest `order` values
INSERT INTO #tmp
SELECT id
,type
,value
,[ORDER]
,user
FROM records
WHERE type = A;
-- old update method
--update records set records.order = #tmp.new_order from records
--join #tmp on #tmp.id = records.id
COMMIT TRANSACTION;
-- new method thanks @Lennart
MERGE INTO records x
USING #tmp
ON #tmp.id = records.id
WHEN MATCHED
THEN
UPDATE
SET records.
ORDER = #tmp.new_order;
DROP TABLE #tmp;
However this solution locks the table for too long, which is not acceptable during business hours.
Is it possible and effective to split this per user?
Take all records of user 1
Reorder using only
order
values of given records (not sure how to do this)Update records table
Repeat for next user
I understand that the whole process will take much longer, but rest of the table will not be locked for other operations?
I am looking for a SQL solution.
UPDATE
I believe I did not elaborate enough.
Column "order" is unique. I am interested if there is a SQL way to:
Get all records for each user. e.g.:
select * from records where user = 1;
Sort records for each user using values available. In case of user=1 we have only values (1,2,7) to operate with.
Update order column in records table
Desired result for give example:
------------------------------------
| id | type | value | order | user |
------------------------------------
| 1 | A | 123 | 7 | 1 |
| 2 | B | 231 | 2 | 1 |
| 3 | B | 213 | 3 | 2 |
| 4 | A | 222 | 4 | 2 |
| 5 | A | 22 | 5 | 3 |
| 6 | B | 11 | 6 | 4 |
| 7 | C | 99 | 1 | 1 |
------------------------------------
UPDATE #2: This is what I theoretically meant. However in practice this will lock the table for much longer periods than current solution and, over time, make all operations with the table slower.
DECLARE @curr_usr INT
DECLARE curr CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT user
FROM records
-- foreach user
OPEN curr
FETCH NEXT
FROM curr
INTO @curr_usr
WHILE @@fetch_status = 0
BEGIN
-- get user entries
SELECT *
,row_number() OVER (
ORDER BY type
) AS new_order
INTO #tmp
FROM records
WHERE user = @curr_usr
-- sort available order values for given record set
MERGE INTO #tmp
USING (
SELECT oder
,row_number() OVER (
ORDER BY
[ORDER]
) AS order_seq
FROM records
WHERE user = @curr_usr
) x
ON x.order_seq = #tmp.new_order
WHEN MATCHED
THEN
UPDATE
SET #tmp.
[ORDER] = x.
[ORDER];
-- update main table
MERGE INTO records
USING #tmp
ON #tmp.id = records.id
WHEN MATCHED
THEN
UPDATE
SET records.
[ORDER] = #tmp.
[ORDER];
FETCH NEXT
FROM curr
INTO @curr_usr
TRUNCATE TABLE #tmp
END
CLOSE curr
UPDATE #3 Some clarification about data amount and time
There are roughly 600k records for ~10k users.
solution #1 (stated in question) takes ~ 29 seconds
solution adapted from @lennart answer takes ~ 27 seconds
solution adapted from @daniel answer takes ~ 26 seconds
This is test data, with no users and services working on it, sorter being the only one. I believe that achieving <10 seconds would be enough for clients not to notice the lag.
It appears that I will have to adapt @daniel solution, limited to user where user = ?
and call this for each user separately with delay between calls, this way clients should not notice any delays, right?