SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem in Insert with On Duplicate Key Update

    Hi..

    I have code for importing data from .xml file to database.

    My issue is the On Dupliicate Key Update did not work, ti save again the data even the data is already exist..

    here is my code:

    Code:
    $sql = "INSERT INTO sales_order (ProductType,WorkOrder,POIssueDate,SalesMonth) 
          VALUES 
          ('$ProductType','$WorkOrder','$POIssueDate','$SalesMonth')
          ON DUPLICATE KEY UPDATE
          ProductType = '$ProductType', WorkOrder = '$WorkOrder', POIssueDate = '$POIssueDate', SalesMonth = '$SalesMonth'" or die(mysql_error());
    For example I attach file then I attahc again but I update some data I want it to update on database if the ProductType is already exist and add if not.

    I attach my sample upload file. and You can see on the second upload the ProductType P101 change the sales month.

    In my query the data that was second upload was insert again it did not update the existing data.I want the result should be the second upload not mix the first and second upload. Now the data was duplicated, thats my problem. I want it to update if the ProductType is already exist.


    Thank you
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    The Netherlands
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you are missing a key in your query.

    Key Name
    1 Test
    2 Test 2
    3 Test 3

    $sql = "INSERT INTO tabe (Key, Name)
    VALUES
    ($key, $name)
    ON DUPLICATE KEY UPDATE
    Name = $name" or die(mysql_error());


    This will update record 1

    $sql = "INSERT INTO tabe (Key, Name)
    VALUES
    (1, 'Test update')
    ON DUPLICATE KEY UPDATE
    Name = 'Test update'" or die(mysql_error());

    This will insert new record. Key doesn't have to be filled to insert new record if you made it auto increment.

    $sql = "INSERT INTO tabe (Key, Name)
    VALUES
    (4, 'Test new record')
    ON DUPLICATE KEY UPDATE
    Name = 'Testupdate'" or die(mysql_error());


Bookmarks

Posting Permissions

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