Hello Clemens, Friday, July 4, 2014, 1:43:47 PM, you wrote:
CL> 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? CL> CREATE TEMP TABLE cat2 AS CL> SELECT (SELECT COUNT(*) CL> FROM categories AS previous CL> WHERE previous.name <= categories.name) AS new_id, CL> id AS old_id, CL> name CL> FROM categories; CL> DELETE FROM categories; CL> INSERT INTO categories CL> SELECT new_id, name CL> FROM cat2 CL> ORDER BY new_id; CL> UPDATE products CL> SET category_id = (SELECT new_id CL> FROM cat2 CL> WHERE old_id = products.category_id); CL> DROP TABLE cat2; Thank you. Regards, Eric _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
