1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Insert And Update Existing In Same Query

Discussion in 'MySQL' started by Scoty, Feb 5, 2013.

  1. #1
    I'm trying to insert a query and update any rows that already exist at the same time, I know it's bad practise to use the SQL that phpMyAdmin outputs, this was just to get it to work initially then I'll clean it up. So I read to use ON DUPLICATE KEY UPDATE, but I haven't managed to find anything to really explain the correct syntax I should be using.

    INSERT INTO `play_products` (`id`, `prod`, `img`, `price`, `del`, `cat`, `sup`) VALUES ('684271539', 'BL 2', 'url', '19.85', '0.00', 'PS3', 'partner');
    ON DUPLICATE KEY UPDATE `play_products` (`prod`, `img`, `price`, `del`, `cat`, `sup`);
    Code (markup):
    It gives the error:
    #1062 - Duplicate entry '684271539' for key 'PRIMARY'
    Can anyone suggest the correct syntax or a better method? I have thousands of records to add/update every day so really need something that can work in bulk and as efficient as possible with time (will be automated of course, but I want to keep the processing time to a minimum)

    Regards
     
    Last edited: Feb 5, 2013
    Scoty, Feb 5, 2013 IP
  2. Scoty

    Scoty Active Member

    Messages:
    620
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    60
    #2
    As I couldn't get it to work (using various methods obtained through search engines) I've decided to just split the process in PHP:

    $query = mysql_query("SELECT id FROM products WHERE id='$id'") or die(mysql_error());
    if (mysql_num_rows($query) > 0)
    {
    //update..
    }
    else
    {
    //insert..
    }
    PHP:
    I have no preference to how it's done, just assumed it would be more efficient to do it in a single query, so I'm still open to suggestions if they will reduce the processing time/resources needed.
     
    Scoty, Feb 5, 2013 IP