MySQL Forums
Forum List  »  InnoDB

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
224
March 23, 2017 06:37AM


Sorry, you can't reply to this topic. It has been closed.

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.