On 5/9/05, Brown, Dave <[EMAIL PROTECTED]> wrote:
>
> The question I have is: If a row gets deleted, how do I sequentially reorder
> the remaining
> rows? For example:
>
> Start with this:
> a, 1
> b, 2
> c, 3
> d, 4
>
> now delete the row with 'b':
>
> a, 1
> c, 3
> d, 4
>
> I'm trying to figure out how to write an update statement which will reorder
> this back to:
>
> a, 1
> c, 2
> d, 3
I think this trigger will do it on table playlist
CREATE TRIGGER keep_in_order_delete AFTER DELETE ON playlist
BEGIN
UPDATE playlist SET ord = ord-1 WHERE ord > old.ord;
END;
Full example:
CREATE TABLE x (
name VARCHAR(10),
ord INTEGER
);
INSERT INTO x VALUES("a", 1);
INSERT INTO x VALUES("b", 2);
INSERT INTO x VALUES("c", 3);
INSERT INTO x VALUES("d", 4);
SELECT * FROM x;
SELECT datetime('now');
CREATE TRIGGER keep_in_order_delete AFTER DELETE ON x
BEGIN
UPDATE x SET ord = ord-1 WHERE ord > old.ord;
END;
DELETE FROM x WHERE ord = 2;
SELECT * FROM x;
SELECT datetime('now');
DROP TABLE x;
--
Gabor Szabo
http://www.szabgab.com/
