The values ​​of the shift down the chain in the table

Question

Provided that I have the following result from the 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 remove the row identifier 8 and push the values ​​in the “value” field down so that each row has the value of the previous record, but affects only those where the field type is “the same” as the deleted row (in this case, “A ").

That is, removing the identifier of line 8 should ultimately lead to the following:

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

ID 10 inherits the value from ID 8, then ID 11 inherits from ID 10 and so on. However, note that strings of type "B" are not affected.

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

(Also, I would prefer not to use triggers, as I intend to encapsulate all the application logic in 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 

Take a look at sqlfiddle .

---

UPDATE

I did not understand that you really need to update the base table. To do this, you can use a small hacker to effectively do the same in the UPDATE (you cannot directly assign user variables, so instead assign a column to concatenate its new value and an empty 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; 

Take a look at sqlfiddle .

Answer 2

This task is performed very easily using window / analytic functions. Since MySQL does not have these, they can be modeled with the following restriction:

• You must have a unique sort field.

I used id for this purpose.

The following query will list each row in your table, using type as a section 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 added ORDER BY only for visibilty, not required in the final request.

The following query allows you to join 2 results and have a way to “shift values” as follows:

 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 two 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 for SQL Fiddle (not updates).

Answer 3

I suggest you use InnoDB tables so that you can run 2 queries in one transaction. I would do this:

 Step 1: Start a transaction on the table Step 2: Get the record that is to be deleted (eg 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