3

I have a mySql table like this (simplified)

Id*| Text |  Pos (integer)
-----------
A  | foo  |  0
B  | bar  |  1
C  | baz  |  2
D  | qux  |  3

Now, after I delete a row, I want to update the Pos value on the remaining rows so that no "holes" or gaps are to be found.

For example if I row with Id='C' is deleted, the remaing table should be:

Id*| Text |  Pos (integer)
-----------
A  | foo  |  0
B  | bar  |  1
D  | qux  |  2

Is this possible in a single query?

UPDATE Based on the accepted answer this was the solution to my problem:

START TRANSACTION;
SELECT @A:=pos FROM table_name WHERE Id= 'C';
DELETE FROM table_name WHERE Id = 'C';
UPDATE table_name SET Pos = Pos - 1 WHERE Pos > @A;
COMMIT;
Muleskinner
  • 14,150
  • 19
  • 58
  • 79

2 Answers2

2

You can achieve this by creating a AFTER DELETE TRIGGER on table,

or by using transactions:

START TRANSACTION;

SELECT Pos 
INTO @var_pos
FROM table_name
WHERE id = 'C';

DELETE 
FROM table_name
WHERE id = 'C';

UPDATE table_name
SET Pos = Pos - 1
WHERE Pos > @var_pos;

COMMIT;
Omesh
  • 27,801
  • 6
  • 42
  • 51
  • Thank you, this is very close to what I want. Just not working in my case as the ID is not ordered (its a random string) – Muleskinner Sep 12 '12 at 10:55
  • You're welcome! You just need to add `ORDER BY id ASC` in your `UPDATE` query. Try updated answer. – Omesh Sep 12 '12 at 10:56
  • But still, as you use "id > 'C'" and id is not ordered it is not working. – Muleskinner Sep 12 '12 at 10:59
  • Ok. this might be happening because of `VARCHAR` data type of field `id`. Please try updated query. – Omesh Sep 12 '12 at 11:04
  • Brilliant thank you / you showed me the way. Your answer was not working, threw an error: You can't specify target table 'table_name' for update in FROM clause. I have updated my question with a workable solution based on your answer. Thanks again – Muleskinner Sep 12 '12 at 11:11
1

I think this should work, (I haven't tested it) you can run this statement after any delete

update t set t.Pos=a.iterator
from tablename t 
join(
     SELECT @i:=@i+1 AS iterator, t.id
     FROM tablename t,(SELECT @i:=0) r)a
on a.id=t.id
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58