homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

constraint on import

5+ Year Member

Msg#: 4292459 posted 2:53 pm on Apr 5, 2011 (gmt 0)

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:
`Date` datetime DEFAULT NULL,
`Symbol` varchar(6) DEFAULT NULL,
`ClosingPrice` decimal(8,2) DEFAULT NULL,
KEY `Symbol` (`Symbol`)

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?
end if;
end $$

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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved