Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: Insert with Where Not Exists

    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);
    Last edited by nitrous; 10-23-09 at 20:00.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Quote Originally Posted by r937
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •