I import data regularly form the following function: load data local infile 'C:\\Documents and Settings\\J\\Desktop\\import.csv' into table data fields terminated by ',' enclosed by '''' lines terminated by '\n';
I cannot edit all of the raw data so what I want to so is prevent duplicates entering the database. A duplicate is a row that has the same same symbol and same date. So, 1 entry for AAA '2011-04-01' is allowed but not AAA '2011-04-01' & AAA '2011-04-01'
What is the best way to put a constraint on this while importing? Furthermore, I would like to allow the data be updated if the ClosingPrice has changed.
I thought a trigger might work: DELIMITER $$ create trigger duplicate_check before insert on data for each row begin if new.Date = (SELECT Date FROM data WHERE Date=new.Date AND Symbol=new.Symbol) then call fail(’Duplicate data’); #is there a way just to abort that 1 row insert here? #where is the else function to allow the insert? end if; end $$ DELIMITER ;
Where is the else function to allow the insert? Secondly, as the imported data doesn't have column labels how does the insert know which column is which as at the moment it just imports them in column order I think.