MySQL Forums :: InnoDB :: Inseret records from one table to many


Advanced Search

Inseret records from one table to many
Posted by: atjun yadav ()
Date: March 23, 2017 06:37AM

I have a table called t_csv.
csv_id category parent_category product generic
1 Unanai Unanai-parent Unanai-prod gen-1
2 Aqu Aqu-parent Aqu-prod gen-2
3 Afga Afga-parent Afga-prod gen-3
4 Indana Indana-parant Indana-prod gen-4
5 Tib Tib-parent Tib-prod gen-5

with the above csv file i want to insert into the following two table so that it looks like under:

t_pcategory
parent_id parent_category
1 Main
2 pc-22
3 Unanai-parent
4 Aqu-parent
5 Afga-parent
6 Indana-parant
7 Tib-parent


and
t_category
id category parent_category status
1 Ayurvedic Meds 1 Active
2 Pet Meds 1 Active
3 Best Seller 1 Active
4 Unanai 3+ Active
5 Aqu 4+ Active
6 Afga 5+ Active
7 Indana 6+ Active
8 Tib 7+ Active

I have been able to insert the data properly here is the code


$query = $this->db->query("
INSERT INTO t_pcategory (parent_category)
SELECT parent_category from t_csv where parent_category NOT IN (select parent_category from t_pcategory)
);



$query = $this->db->query("
INSERT INTO t_category (category)
SELECT category from t_csv where category NOT IN (select category from t_category)
); );

But it insert the category only and not the parent_category which should come
from t_pcategory parent_id which is unique and autogen
perhaps I could not join it properly

I short i am unable to insert these above values 3+ 4+ 5+ 6+ 7+ which is the main issue i try to do it in the following ways but could not get the result

insert into t_category (category, parent_category)
(select t1.category, t3.parent_id
from t_csv t1
left join t_category t2 on t1.category = t2.category
left join t_pcategory t3 on t1.parent_category = t3.parent_category
where t2.id is null and t3.parent_category is not null)

Please help

Options: ReplyQuote


Subject Views Written By Posted
Inseret records from one table to many 64 atjun yadav 03/23/2017 06:37AM
Re: Inseret records from one table to many 34 Peter Brawley 03/23/2017 11:24AM
Re: Inseret records from one table to many 38 atjun yadav 03/23/2017 12:37PM
Re: Inseret records from one table to many 63 Peter Brawley 03/23/2017 01:18PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.