how about recreating the table w/o the autoincrement, then reload the data, then alter table to reimplement the autoincrement?
> -----Original Message----- > From: Matthew [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 04, 2003 7:12 PM > To: Alex E.Wintermann; [EMAIL PROTECTED] > Subject: Re: Tree-like structure: make it simply > > > I think I can help with questions 1 and 3... see below, > > > > ----- Original Message ----- > From: "Alex E.Wintermann" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, December 04, 2003 11:44 PM > Subject: Tree-like structure: make it simply > > > > Hello mysql, > > > > I have some table: > > /* BEGIN DUMP *************************************************/ > > CREATE TABLE `sp_tovar_vid` ( > > `id` int(11) NOT NULL auto_increment, > > `id_tovar_vid` int(11) NOT NULL default '0', > > `name` varchar(100) NOT NULL default '', > > `description` varchar(255) NOT NULL default '', > > UNIQUE KEY `id` (`id`) > > ); > > > > INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category'); > > INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', ''); > > INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', ''); > > INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', ''); > > INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', ''); > > INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', ''); > > INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)'); > > INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)'); > > INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)'); > > INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)'); > > INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)'); > > INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)'); > > INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', > '(to subcat.2 > > to cat.1)'); > > /* END DUMP ****************************************************/ > > > > _QUESTION 1_: in what way should i run query to restore table with > > `id`' like in dump? (`id` is auto_increment) > > > > > either omit the `id` field when re-loading the data, e.g. > > INSERT INTO `sp_tovar_vid` > (`id_tovar_vid`,`name`,`description`) VALUES (0, > 'root', 'root category'); > > or, replace the `id` values with an empty string when > re-loading the table > data, e.g. > > INSERT INTO `sp_tovar_vid` VALUES ('', 0, 'cat.1', ''); > > both techniques prompt mysql to reassign the `id` values. > > note that I do not think it is wise/possible to run a query > to restore (or > clean up) the auto_increment values without re-loading the table data. > > > > > > _QUESTION 2_: how to display table with columns= > > ('parent_category.name' ,'category.name')? > > i tried this: > > /***/ > > SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid` > > AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`; > > /***/ > > How to make it more simply? > > > > _QUESTION 3_: how to display string: > > "subsubcat 1 - subcat.2 - cat.1" > > if we have only `id`=18 ? > > > > use SELECT IF(id=18, true_expression, false expression) FROM table... > > I'm not clear what you mean by "subsubcat 1 - subcat.2 - > cat.1"... minus, or > some sort of string concatenation? > > true expression might be field1 - field2, or CONCAT(field1, > field2, ...) > > > > > -- > > Best regards, > > Alex mailto:[EMAIL PROTECTED] > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
