Shift values down the chain in a table

Question

Provided that I have the following result set from a mysql database table:

+----+------+-------+
| ID | type | value |
+----+------+-------+
|  8 |    A |  1435 |
|  9 |    B |  7348 | 
| 10 |    A |  1347 | 
| 11 |    A |  3478 | 
| 12 |    A |  4589 | 
| 13 |    B |  6789 |
+----+------+-------+

I would like to delete row ID 8 and push the values in the field 'value' down, in such a way that every row has the value of previous entry, but affecting only those where the field 'type' is the same as the row being deleted ('A' in this case).

That is to say, deleting row id 8 should eventually yield the following:

+----+------+-------+
| ID | type | value |
+----+------+-------+
|  - |    - |    -  | *
|  9 |    B |  7348 |   |
| 10 |    A |  1435 | * |
| 11 |    A |  1347 | * |
| 12 |    A |  3478 | * |
| 13 |    B |  6789 |   V
+----+------+-------+

ID 10 has inherited the value from ID 8, then ID 11 inherits from ID 10, and so on. Notice however how rows having type 'B' are unaffected.

So the question: Is there any way to perform this "shift" of values without having to query and update each row one by one? In an ideal world I would do one query to do shift and then another to delete the row, but I'm not quite sure if this is possible at all.

(Also I would rather not use Triggers, since I intend encapsulate all the application logic within the application itself)

Answer 1

SET @remove_id = 8;

SELECT ID, type, value FROM (
  SELECT   ID,
           type,
           CAST(IF(type <> @type OR ISNULL(@val), value, @val) AS UNSIGNED)
             AS value,
           @type := IF(ID   = @remove_id, type, @type),
           @val  := IF(type = @type, value, @val)
  FROM     my_table JOIN (SELECT @type := NULL, @val := NULL) AS z
  ORDER BY ID ASC
) AS t
WHERE ID <> @remove_id

See it on sqlfiddle.

---

UPDATE

I hadn't realised you actually wanted to update the underlying table. For that, you can use some slight hackery to effectively do the same thing in an UPDATE statement (one can't assign to user variables directly, so instead assign to a column the concatenation of its new value and a null string formed from taking the first 0 characters of the newly assigned user variable):

SET @remove_id = 8, @type = NULL, @val = NULL;

UPDATE my_table SET
  value = IF(
    type <> @type OR ISNULL(@val),
    value,
    CONCAT(@val, LEFT(@val := value, 0))
  ),
  type = CONCAT(type, LEFT(
    @type := IF(
      ID <> @remove_id,
      @type,
      CONCAT(type, LEFT(@val := value, 0))
    )
  , 0))
ORDER BY ID ASC;

DELETE FROM my_table WHERE ID = @remove_id;

See it on sqlfiddle.

Answer 2

This task is accomplished very easy using window/analytical functions. As MySQL do not have such, they can be simulated with the following restriction:

• you should have a unique field to sort on.

I have used id for this purpose.

The following query will enumarte each row in your table, using type as a partition indicator:

SELECT t.id, t.type, t.value,
  (SELECT count(*) FROM testbed WHERE type = t.type AND id <= t.id) AS rownum
  FROM testbed t
 ORDER BY t.type, t.id;

I've added ORDER BY only for visibilty, not required in the final query.

Next query allows you to join 2 results and have way to “shift values” the needed way:

SELECT c.id AS c_id, c.type AS c_type, c.value AS c_value,
       p.id AS p_id, p.type AS p_type, p.value AS p_value
  FROM (SELECT t.id, t.type, t.value,
               (SELECT count(*) FROM testbed
                 WHERE type = t.type AND id <= t.id) AS rownum
          FROM testbed t) AS c
  LEFT JOIN (SELECT t.id, t.type, t.value,
                    (SELECT count(*) FROM testbed
                      WHERE type = t.type AND id <= t.id) AS rownum
               FROM testbed t) AS p
         ON c.type = p.type AND c.rownum = p.rownum + 1
 ORDER BY c.type, c.id;

Finally, your task is accomplished with the following 2 queries, UPDATE and DELETE:

UPDATE testbed AS t
JOIN (
  SELECT c.id AS c_id, c.type AS c_type, c.value AS c_value,
         p.id AS p_id, p.type AS p_type, p.value AS p_value
    FROM (SELECT t.id, t.type, t.value,
                 (SELECT count(*) FROM testbed
                   WHERE type = t.type AND id <= t.id) AS rownum
            FROM testbed t) AS c
    LEFT JOIN (SELECT t.id, t.type, t.value,
                      (SELECT count(*) FROM testbed
                        WHERE type = t.type AND id <= t.id) AS rownum
                 FROM testbed t) AS p
           ON c.type = p.type AND c.rownum = p.rownum + 1
  ) AS s
  ON t.id = s.c_id
  SET t.value = s.p_value
 WHERE t.value = 'A'; -- you can use more complex predicate(s) here

DELETE FROM testbed WHERE id = 8; -- make sure both predicate(s) match

You can check this query on SQL Fiddle (not the updates).