Complex Insert Into Select Query Help Needed

[Monotoba]

Hi All, I have an issues with an insert into select query. The working query is as follows:

 

INSERT INTO auto_xref x

SELECT "", m.id AS "make_id", n.id AS "model_id", y.id AS "year_id", m.id AS "engine_id",

a.id AS "airfilter_id", f.id AS "fuelfilter_id", o.id AS "oilfilter_id"

FROM import i

JOIN auto_make m ON i.make = m.name

JOIN auto_model n ON i.model = n.name

JOIN auto_year y ON i.year = y.production_yr

JOIN auto_engine e ON i.engine = e.name

JOIN airfilter a ON i.airfilter = a.part_no

JOIN fulefilter f ON i.fuel = f.part_no

JOIN oilfilter o ON i.oil = o.part_no

 

What I need is to make this query only insert records that don't already exists in the table auto_xref. I have tried various forms of "join on x.field = null AND x.anotherfield = null" but have yet to get anything to work corectly. All hep is greatly appreciated!

 

Thanks

[toplay]

I'm not sure what the primary key of your auto_xref table is.

 

But you don't say "fied = null" but rather "field IS NULL"  or "field IS NOT NULL" depending on what you want.

 

If you don't mind the existing data deleted and inserted again, then you can always user "REPLACE INTO" instead of "INSERT INTO".

 

 

[Monotoba]

First, pardon my typos, I'm a bit under the weather today...

 

What I am trying to accomplish is to update the db from a csv file. The csv file contains rows like:

 

make, model, year, engine, fuelfilter, airfilter, oilfilter

 

The csv file contain over 30K records and must be normalized in the db. So I have created a table for each column in the csv: make_tbl, model_tbl, year_tbl, etc... The csv does not include an id field. All my db tables do. So the most table look like make('id', 'name').

 

the auto_xref table is a bridge (pivot) table used to link all the record properties back together and includes only corisponding ids: auto_xref('make_id', 'model_id', ... 'oilfilter_id').

 

I run queries to insert the various record properties in to the correct table like this:

 

SELECT DISTINCT a.field

FROM import_tbl a

LEFT JOIN update_tbl b ON a.field = b.field

WHERE b.field IS NULL AND a.field !=''";

 

Now I need to make the auto_xref query insert only new records. Replace Into will not work as there is no key in the cvs table...

 

I hope this larger picture will help convey my needs better...

 

Thanks for your help, I can use and will try all the ideas passed my way :-)