homepage Welcome to WebmasterWorld Guest from 54.234.59.94
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
constraint on import
jackvull




msg:4292461
 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:
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:
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.

 

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