Inseret records from one table to many
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
Subject
Views
Written By
Posted
Inseret records from one table to many
905
March 23, 2017 06:37AM
617
March 23, 2017 11:24AM
550
March 23, 2017 12:37PM
749
March 23, 2017 01:18PM
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.