Erik Jensen wrote:
> > If the list of categories does not change, you could assign the
> > category IDs so that they have the same order as the names.
>
> Yes, that was my idea too if there is no other easy solution.
>
> Is there an easy/elegant way to re-order the "category" table and
> update the "products" table with the new category_ids?
CREATE TEMP TABLE cat2 AS
SELECT (SELECT COUNT(*)
FROM categories AS previous
WHERE previous.name <= categories.name) AS new_id,
id AS old_id,
name
FROM categories;
DELETE FROM categories;
INSERT INTO categories
SELECT new_id, name
FROM cat2
ORDER BY new_id;
UPDATE products
SET category_id = (SELECT new_id
FROM cat2
WHERE old_id = products.category_id);
DROP TABLE cat2;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
