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';
The table structure is:
CREATE TABLE `data` (
`Date` datetime DEFAULT NULL,
`Symbol` varchar(6) DEFAULT NULL,
`ClosingPrice` decimal(8,2) DEFAULT NULL,
KEY `Symbol` (`Symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
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:
create trigger duplicate_check before insert on data for each row
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?
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.