|
|
OT: SQListi again
|
A quick one, I've got three tables I need to populate from one file.
I've emptied this file into a temp table.
The first table needs the category, the second needs to populate with
a sub_category and also the previous category id.
I then need to fill the third with the left over data (i.e. the data
about the products). This also needs the category and sub category.
Any ideas the best way about going around this with SQL only? I'm
currently writing a script but am thinking it may have a few holes in
it.
|
Populate first the category table with a SELECT DISTINCT of the category
data from the temp table. Then proceed to sub_category with the same
approach. Then you're ready to load the items without foreign key issues.
If this is a repetitive process, you will need to add to the select distinct
category_id from temp table a "and category_id not in (select category_id
from categories)", same for subcategories.
That is Oracle SQL as in standard SQL92, I don't know if your MySQL version
will handle that.
|
|
What do the 4 tables look like - create scripts would be handy..
If you had 8-ish rows of sample data in them then that would be good
to see as well, making it much clearer what your intention / problem
is...
You'd need to remind me which SQL flavour you're using as well, in
case it needs to get a bit scripty.
|
I'm using MySQL ....
The create scripts are .. .
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`deleted` tinyint(1) default '0',
PRIMARY KEY (`id`),
KEY `id` (`id`)
);
CREATE TABLE `sub_categories` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) character set latin1 default NULL,
`category` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `codes` (
`id` int(10) unsigned NOT NULL auto_increment,
`version` int(10) NOT NULL default '1',
`name` varchar(255) default NULL,
`category` int(10) default NULL,
`sub_category` int(10) default NULL,
`created` datetime default NULL,
`code` varchar(100) default NULL,
`notes` varchar(255) default NULL,
PRIMARY KEY (`id`,`version`)
);
Basically the data comes in like ...
4910000000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM OTHER PRINTED MATTER
Calendars 0.00% 17.50%
4909001000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM OTHER PRINTED MATTER
Postcards 0.00% 17.50% Supp Units - Hundred Items
4909009000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM OTHER PRINTED MATTER
Greetings Cards 0.00% 17.50% Supp Units - Hundred Items
4911990000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM OTHER PRINTED MATTER
Photographs 0.00% 17.50%
4911990000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM OTHER PRINTED MATTER
Posters 0.00% 17.50%
4911990000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM OTHER PRINTED MATTER
Tickets 0.00% 17.50%
8523403900 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM MUSIC Music CD's &
Mini Discs 3.50% 17.50%
4904000000 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM MUSIC Music Books &
Music Scores 0.00% 0.00%
8523405100 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM FILM DVD's 1.50%
17.50%
8523293900 BOOKS & OTHER PRINTED MATTER, MUSIC & FILM FILM Video Cassettes
1.50% 17.50%
4202929890 EQUESTRIAN BAGS Saddle Bags etc., Of Textile 2.70% 17.50%
4202921900 EQUESTRIAN BAGS Saddle Bags etc., Of Plastic 2.70% 17.50%
So, there are multiple categories, then multiple sub-categories and finally
numerous items under each.
|
insert into categories (name)
select distinct your_category_name_column from temp_table ;
insert into sub_categories (name, category)
select distinct t.your_sub_category_name_column, c.id
from categories c, temp_table t
where t.your_category_name_column = c.name ;
insert into codes (version, name, category, sub_category, created, code,
notes)
select t.your_version_column, t.your_name_column,
c.id, s.id, sysdate /* oracle */, t.your_code_column, t.your_notes_column
from categories c, sub_categories c, temp_table t
where s.name = t.your_sub_category_name_column
and s.category = c.id ;
|
OOohhhh! Equestrian goods ! Nice.
|
|
|
|
|
|