Duplicate entry '1-1110' for key 'INDEX' while removing element from Index

Question

I have a query to update index if something added in between

update My_Table
     set NS_LEFT = NS_LEFT + 10
     where NS_THREAD = parentThread and NS_LEFT > oldRight
     order by NS_LEFT desc

Its working fine -

Now I if I have to delete something in between then I am using query as below

update My_Table
    set NS_LEFT = NS_LEFT - 10
    where NS_THREAD = parentThread and NS_LEFT > oldRight
    order by NS_LEFT desc

Its is not working and throwing error of duplicate Index -

[Code: 1062, SQL State: 23000] (conn=1517) Duplicate entry '1-1110' for key 'INDEX'

Index(NS_THREAD,NS_LEFT )

How do solve for delete element

Note This is my work around for MariaDB only for other other Database its working without OrderBy (Why.. its still open question for me)

Perhaps you need order by to be ASC in the second query; if you leave it DESC, the highest numbers get moved down before a "space" has been made for them. — Uueerdo, May 17 '19 at 20:19
@Uueerdo I tried with that but it again shows duplicate key error in ASC order — Afgan, May 17 '19 at 20:21
If my guess is correct, you are using a gap of 10 to decrease the frequency these shifts are required; but you are performing the shifts under the assumption that the gaps never actually get used. Example: (10), (10, 20), (10, 15, 20), (10, 15, 20, 30), (10, 15, 20, 25, 30)... removing 20 would attempt to shift 25 to 15. If your version of MariaDB supports CTEs and windowing functions, you might want to try something like [this](https://stackoverflow.com/questions/4358613/using-window-functions-in-an-update-statement). _it's for postgres, but should point you in the right direction._ — Uueerdo, May 17 '19 at 21:34
amount 10 is not fixed, it may vary according to the child element. — Afgan, May 18 '19 at 22:06

Uueerdo · Accepted Answer · 2019-05-20T16:28:54.403

What I have done in the past when controlling the order is not an option is to perform two updates. The first shifts the group way up past any currently used values, insuring no collisions. The second then shifts them to where they should be. In general form, the idea can be illustrated with this:

UPDATE aTable SET somevalue = somevalue + 10000 WHERE somevalue > x;
UPDATE aTable SET somevalue = somevalue - 10000 - y WHERE somevalue > x + 10000;

"10000" is just a value that will push the range past collision, y is the amount you actually want to shift them. Obviously if there are already values around 10000, the number will need to be different. To avoid having to query for a safe value, another option if the design permits....

If negative values are not used and the table design allows negative numbers, this is version of the process is a little simpler to apply:

UPDATE aTable SET somevalue = somevalue * -1 WHERE somevalue > x;
UPDATE aTable SET somevalue = (somevalue * -1) - y WHERE somevalue < 0;

This presumes there are not normally negative values, and to be safe the updates should be performed within a transaction (along with the original delete) so that a potential concurrent applications of this solution do not collide. (Edit: Note that transactions/concurrency requirement goes for both forms I have presented.)

Edit: Oh, I just noticed Gordon's answer was quite similar... the bare minus signs looked like flecks on my screen. If Gordon's didn't work, this won't either.

Thanks ....This works for me, I have one doubt while incrementing NS_LEFT in my first example, is it correct way to do with order by? if not then can I do same with your provided ans. — Afgan, May 20 '19 at 09:12
i think the second update should be `UPDATE aTable SET somevalue = somevalue - 10000 - y WHERE somevalue + 10000 >= x;` <- notice ive changed `somevalue + 10000 >= x` in the `WHERE` clause to make sure the query only picks up the "anti collision range".. — Raymond Nijland, May 20 '19 at 12:12
@RaymondNijland I think you meant somevalue > x + 10000; but since the first update shifts everything above x to begin with, nothing should come between the shifted items and x unless concurrency is not being handled (through transactions or elsewhere). — Uueerdo, May 20 '19 at 16:31

score 1 · Answer 2 · answered May 17 '19 at 20:10

1

That happens. One solution is to do two updates:

update My_Table
    set NS_LEFT = - (NS_LEFT - 10)
    where NS_THREAD = parentThread and NS_LEFT > oldRight
    order by NS_LEFT desc;

update My_Table
    set NS_LEFT = - NS_LEFT
    where NS_THREAD = parentThread and NS_LEFT < 0;

answered May 17 '19 at 20:10

Gordon Linoff

1,242,037
58
646
786

Why 2 , can you please explain ? – Afgan May 17 '19 at 20:12
1st query runs fine, but second one again - [Code: 1062, SQL State: 23000] (conn=1517) Duplicate entry '1-1116' for key 'INDEX' – Afgan May 17 '19 at 20:13
@Afgan . . . In that case, your logic would appear to be wrong. You are creating duplicates in columns that should not have them. – Gordon Linoff May 17 '19 at 20:17
Why we cant we use single as while adding element , and This is my work around for MariaDB only for other other Database its working without OrderBy (Why.. its still open question for me) – Afgan May 17 '19 at 20:20

Duplicate entry '1-1110' for key 'INDEX' while removing element from Index

2 Answers2