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)

On query? I doubt it. Each update is a new query. I would recommend a SQL procedure, but if you do not want those I guess you are stuck to a loop construction. To speed things up, use transactions. — clentfort, May 31 '12 at 12:41
I think it is possible to collect all the rows that need to be updated in a temptable (#temptable). From thereon, you can calculate the new value and use a single update statement to update your main table from the temptable. Then you don't need a loop contstruction. — M.Schenkel, May 31 '12 at 13:25
EDIT: You can probably use ROW_NUMBER() to shift the rows. Since this is not available in mysql, check the answer of OMGPonies on this question: http://stackoverflow.com/questions/1895110/row-number-in-mysql — M.Schenkel, May 31 '12 at 13:33

eggyal · Accepted Answer · 2012-06-01T08:41:05.253

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.

Upvoted because this is pretty cool for a single query, but I was actually looking for something that would not only select but also record the result back to the DB. — Mahn, Jun 01 '12 at 01:20
@Mahn: I hadn't realised that. See my updated answer for a somewhat hack-ish, but hopefully a little easier solution than your accepted answer. :) — eggyal, Jun 01 '12 at 08:41
Beautiful, thank you. It seems there's a lot one can do with conditional expressions within the queries themselves, I should probably look more into them. — Mahn, Jun 01 '12 at 12:25

score 2 · Answer 2 · answered May 31 '12 at 13:49

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).

This does the trick, thanks! Although I fear it may become a pain to maintain, but it's probably as good as it can get. — Mahn, Jun 01 '12 at 01:28
I accepted the previous answer because I value brevity (easier to maintain), but you have my upvote nonetheless. — Mahn, Jun 01 '12 at 12:28

score 0 · Answer 3 · answered May 31 '12 at 13:06

I would suggest you use InnoDB tables so you can run the 2 queries in a single transaction. I would do this:

Step 1: Start a transaction on the table
Step 2: Get the record that is to be deleted (e.g. ID #8)
Step 3: Issue a query DELETE FROM tablename WHERE `ID`=$record_id
Step 4: Issue a query UPDATE tablename SET `value`='former_value' WHERE `type`='former_type' LIMIT 1
Step 5: if all operations were successful, commit the transaction else rollback
Step 6: End the transaction

Hope this helps

Even if you use a transaction, several queries still mean several trips to the DB which I wanted to avoid. Thanks for replying though. — Mahn, Jun 01 '12 at 01:18

Shift values down the chain in a table

3 Answers3