transfer table data when normalize tables in database

Question

i have a table like this:

Table1:(Have 400 record)

id    category_title       subcategory
----------------------------------------
1     title1               subTitle1.1
2     title1               subTitle1.2
3     title2               subTitle2.1
4     title2               subTitle2.2
5     title2               subTitle2.3

for normalize this table i want to create two table(Table2, Table3) and a relation between those instead of above table. (delete Table1 and create Table2 and Table3)like this:

Table2:

id   category_title
---------------
1     title1   
2     title2   
3     title3   
4     title4

Table3:

id   subcategory     table2_id(FK)
----------------------------------------
1     title1              1
2     title2              1
3     title3              2
4     title4              1
5     title5              2

i can transfer Table1.category_title to Table2.category_title whit this query:

insert into Table2(category_title) (select distinct category_title from Tabe1)

and can transfer Table1.subcategory to Table3.subcategory with this query:

insert into Table3(subcategory) (select subcategory from Tabe1)

but who can fill Table3.table2_id(FK) with correct relation in Table2? (if i want do this with hand i should execute at least 200 query and i don't want do this)

anyone can give me a query or another way to transfer data between this tables? (apologize for my English) Thanks in advance

fthiella fthiella · Accepted Answer · 2014-01-19T09:22:37

You could use these two INSERT queries, the second one with a JOIN:

INSERT INTO table2 (category_title)
SELECT DISTINCT category_title FROM table1;

INSERT INTO table3 (subcategory, table2_id)
SELECT DISTINCT subcategory, table2.id
FROM
  table1 INNER JOIN table2 ON table1.category_title=table2.category_title;

Please see fiddle here.

transfer table data when normalize tables in database

2 Answers