Problems with INSERT INTO ... ON DUPLICATE KEY UPDATE

Discussion in 'MySQL' started by dinki, Nov 21, 2008.

0
  1. #1
    Hey guys .. I'm working on a little project to help my wife track her/our coupons. I found a cool resource for UPC, description and expiration date and am trying to get it into a database. I am using the UPC as the primary key. Unfortunately, I have multiple entries with the same UPC.

    I am trying to write an SQL statement that will insert a new record if the UPC doesn't exist, but if it does, update the description and date. My sql statements import fine, but I end up with only one entry which is the last sql statement.

    Can someone take a look at this and tell me what I'm doing wrong?

    CREATE TABLE `coupondetails` (
    `upc` int(25) NOT NULL,
    `description` tinytext NOT NULL,
    `date` varchar(25) NOT NULL,
    PRIMARY KEY (`upc`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    INSERT INTO coupondetails (upc, description, date) VALUES ("916500992763","Citracal Product, any except Creamy Bites $1 ","12-31-08") ON DUPLICATE KEY UPDATE description="Citracal Product, any except Creamy Bites $1 ", date="12-31-08";
    INSERT INTO coupondetails (upc, description, date) VALUES ("980720373824","BV Coastal Estates Wine, any except Cabernet Sauvignon $2 ","12-31-08") ON DUPLICATE KEY UPDATE description="BV Coastal Estates Wine, any except Cabernet Sauvignon $2 ", date="12-31-08";
    INSERT INTO coupondetails (upc, description, date) VALUES ("986767701002","Baileys Original or Mint Chocolate, etc. 750mL or 1L $2/1 or $5 on 2+ ","12-31-08") ON DUPLICATE KEY UPDATE description="Baileys Original or Mint Chocolate, etc. 750mL or 1L $2/1 or $5 on 2+ ", date="12-31-08";
    INSERT INTO coupondetails (upc, description, date) VALUES ("967387000768","Dreamfields Pasta, any $1 ","3-31-09") ON DUPLICATE KEY UPDATE description="Dreamfields Pasta, any $1 ", date="3-31-09";



    Any help is greatly appreciated!
     
    dinki, Nov 21, 2008 IP
  2. emailbyid

    emailbyid Peon

    Messages:
    78
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try:
    INSERT INTO coupondetails (upc, description, date) VALUES ('',"Citracal Product, any except Creamy Bites $1 ","12-31-08") ON DUPLICATE KEY UPDATE description="Citracal Product, any except Creamy Bites $1 ", date="12-31-08";

    Leave the UPC as NULL it should generate a new UPC when you insert it in...(I think) I know this works on MySQL not sure about SQL
     
    emailbyid, Nov 21, 2008 IP