AJ.DE AJ.DE - 1 month ago 18
MySQL Question

Reflow INT UNSIGNED Series

I have a link table called BoxPeg that ties a series of Pegs to 1 Box.

The catch is that the Pegs are ordered and are treated as an array in the client software.

So we have an INT UNSIGNED column called 'position' in the BoxPeg table.

Every time someone adds a Peg to a Box, we do this:

UPDATE BoxPeg SET position = position+1 WHERE box = '{BOXID}' AND position >= {NEWPEGPOSITION};
INSERT INTO BoxPeg(box, peg, position) VALUES('{BOXID}', '{PEGID}', {NEWPEGPOSITION});


This works fine until the client somehow passes in a new peg position that's too large:

before:
0
1
2
3

after insert 9
0
1
2
3
9


I know that I can get MAX(position) WHERE box = '{BOXID}' on a separate SQL statement before these two lines and make the adjustment to the new peg position based on that, but I would like to simply reflow the entire series in one shot AFTER the insert. This way I'm 100% sure that there are no gaps anywhere in the series from beginning to end.

What would that UPDATE look like?

Consider this situation:

before:
0
2
5
8

after insert 5
0
2
5
6
9

after reflow
0
1
2
3
4

Answer

You could reflow the rows like this:

UPDATE BoxPeg bp
INNER JOIN (
  SELECT *, @n := @n + 1 AS newpos
  FROM BoxPeg, (SELECT @n := -1) x
  WHERE box = '{BOXID}'
  ORDER BY position
) s
 ON bp.box = s.box
AND bp.peg = s.peg
SET bp.position = s.newpos
WHERE bp.position <> s.newpos
;

The subselect uses a variable to calculate new position values for a subset defined by a specific {BOXID}. The UPDATE statement then joins the subselect to BoxPeg and updates the matching rows with the new values. (Thus, when joined to BoxPeg, the subselect is also acting as a filter, limiting the updated rows to those belonging to the given box.)

A SQL Fiddle demo of this query is available.

Note that if the position column was part of a unique constraint, this method might not always work. Although the new values this query generates are unique, MySQL seems to evaluate unique constraints during the update rather than at the end of the statement's execution. Thus, depending on the order in which the rows would be updated, the query might fail if a row was receiving a value identical to that in another, not yet updated, row.