CODEWITHSUNDEEP
mysql
Vlad Papko
Vlad Papko

Reputation: 13302

How to create a new record for every existing record and reference existing records to newly created records?

Here is my current database schema:

category table: 

+------------+------------+
| id         | name       |
+------------+------------+
| 1          | category1  |
| 2          | category2  |
+------------+------------+

It is created in following way: 

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3889 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

I need to create super category record for every existing category record in following way:

Expected result: 

+------------+------------+------------+
| id         | name       | parent_id  |   
+------------+------------+------------+
| 1          | category1  | 3          |
| 2          | category2  | 4          |
| 3          | category1  | 3          |
| 4          | category2  | 4          |
+------------+------------+------------+

The part I have difficulty with is setting parent_id for existing records pointed to id of new records.
Any ideas?

Upvotes: 0

Views: 35

Answers (1)

PeterHe
PeterHe

Reputation: 2766

Need to do a self join:

update category p
inner join category c
on c.name=p.name
set c.parent_id=p.id
where c.id<>p.id

Upvotes: 1

Related Questions