hey guys I cant figure out what i need to change to make this work? version 5.0


Insert into stockchart (symbol,date,open,high,low,close,volume,adjclose) Values ('LVN.V','2009-10-22','0.26','0.37','0.25','0.37','809200','0.37') select symbol,date,open,high,low,close,volume,adjclose from dual WHERE NOT EXISTS (select * from stockchart where symbol='LVN.V' and date='2009-10-22' LIMIT 1);

INSERT IGNORE INTO stockchart (symbol,date,open,high,low,close,volume,adjclose)
VALUES
('LVN.V','2009-10-22','0.26','0.37','0.25','0.37','809200','0.37')

no unfortunately that will not work... none of those values can be the primary key becuase there will be a number of the same with the same symbol...

this is the table:

CREATE TABLE stockchart (
ID int NOT NULL AUTO_INCREMENT,
symbol VARCHAR(50),
date Date,
open DECIMAL(20,2),
high DECIMAL(20,2),
low DECIMAL(20,2),
close DECIMAL(20,2),
volume BIGINT,
adjclose DECIMAL(20,2),
LastUpdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY ( ID )
);

So it has to be done with the where not exists but i cant get it to work

the key to the solution here is given by you in your query in the first post --

WHERE NOT EXISTS
(select * from stockchart
where symbol='LVN.V' and date='2009-10-22' LIMIT 1);


thus, the unique key should be on symbol and date

ALTER TABLE stockchart
ADD UNIQUE ( symbol,date )

now your INSERT IGNORE will work