Insert And Update Existing In Same Query

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

0
  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