Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts

    Update/insert at the same time?

    Is it possible to test for a values existence, and update if exists, insert if not?

    I have the following table:

    Code:
    CREATE TABLE IF NOT EXISTS `characters` (
      `characterID` int(11) NOT NULL,
      `charName` varchar(50) NOT NULL,
      `accountID` int(11) NOT NULL,
      `linkedOn` datetime NOT NULL,
      PRIMARY KEY (`characterID`,`accountID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
    Where characterID/accountID is a key. Occasionally, I'll pull data from an API, and get up to 3 characterIDs (I will already have the accountID). Is it possible to test if the characterID/accountID combo exists, if it does, update the linkedOn with the current date, and if it doesn't, insert it? Or do I have to do 2 queries? Is it acceptable to try to insert the data, knowing it may already exist, and relying on the fact that characterID/accountID is a primary key to make sure no repeats get in?

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Keleth View Post
    Is it possible to test for a values existence, and update if exists, insert if not?

    I have the following table:

    Code:
    CREATE TABLE IF NOT EXISTS `characters` (
      `characterID` int(11) NOT NULL,
      `charName` varchar(50) NOT NULL,
      `accountID` int(11) NOT NULL,
      `linkedOn` datetime NOT NULL,
      PRIMARY KEY (`characterID`,`accountID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
    Where characterID/accountID is a key. Occasionally, I'll pull data from an API, and get up to 3 characterIDs (I will already have the accountID). Is it possible to test if the characterID/accountID combo exists, if it does, update the linkedOn with the current date, and if it doesn't, insert it? Or do I have to do 2 queries? Is it acceptable to try to insert the data, knowing it may already exist, and relying on the fact that characterID/accountID is a primary key to make sure no repeats get in?
    use replace with a where condition using ifnull?

    http://dev.mysql.com/doc/refman/5.0/en/replace.html
    http://dev.mysql.com/doc/refman/5.0/...unction_ifnull

    replace will delete the old row and insert a new one.

    best regards

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Thanks! Reading through that documentation though, do I need the ifnull? It seems, since characterID/accountID are my primary key, it'll delete/insert if it exists, and just insert if it doesn't?

    EDIT: Also, any idea if it is more, less, or as efficient then INSERT ... ON DUPLICATE ? As REPLACE inserts or deletes and inserts, and ON DUPLICATE inserts or updates, any thoughts to which is better? I know as this is only 3 rows, it won't really make any difference, but any idea on longer/larger queries?
    Last edited by Keleth; 04-19-2011 at 05:18 PM.

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Keleth View Post
    Thanks! Reading through that documentation though, do I need the ifnull? It seems, since characterID/accountID are my primary key, it'll delete/insert if it exists, and just insert if it doesn't?
    yes, you are right, i start to think to the problem backwards, starting with existence and i didn't realised that could be useless,

    best regards

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Does anyone know whether REPLACE is more or less efficient then INSERT ... ON DUPLICATE? Or know how I can test it? As mentioned above, I know with 3 rows, its nothing, but I'm just curious for future use.

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Keleth View Post
    Does anyone know whether REPLACE is more or less efficient then INSERT ... ON DUPLICATE? Or know how I can test it? As mentioned above, I know with 3 rows, its nothing, but I'm just curious for future use.
    replace do first a delete then an insert, can't be more efficient then and simple insert. Even if have nothing to delete it must first check if the row exists.
    even if we suppose that replace is internaly optimized i don't see how can be more efficient. All this are based on intuition, i don't have something clear.

    best regards

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Agree with Oesxyl, also based on intuition. But if it's important, run some benchmark tests.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Sorry if I wasn't clear. "INSERT ... ON DUPLICATE ..." is an extension on INSERT that if a row exists with the same primary key, updates instead of inserting. In contrast, REPLACE deletes if it exists, then inserts. I'm not sure if updating or deleting is more efficient.

    As for running a benchmark, how? Create a table with a few hundred thousand rows, and then try running INSERT ... ON DUPLICATE and REPLACE on like half of them? I'm not sure what would be a good number at which I'd get reasonable results.

  • #9
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Keleth View Post
    Sorry if I wasn't clear. "INSERT ... ON DUPLICATE ..." is an extension on INSERT that if a row exists with the same primary key, updates instead of inserting. In contrast, REPLACE deletes if it exists, then inserts. I'm not sure if updating or deleting is more efficient.

    As for running a benchmark, how? Create a table with a few hundred thousand rows, and then try running INSERT ... ON DUPLICATE and REPLACE on like half of them? I'm not sure what would be a good number at which I'd get reasonable results.
    when something depend on a number N of things, row in this case, and i want to get as much information i can with a minimum of tests i use to discover first how the runing time depend on N, more exactly if is liniar or not, so i will do at least 3 tests N, 2*N and 3*N, for a random N. If is liniar, is ok if not and i realy need i will try to do more tests with other values.
    second step here, because you want to compare both insert and replace, is to discover the value of N where both give same runing time, this in case that exists such a value. Then you know that before and after that value one is better then other.
    the biggest problem here is, in my opinion, to avoid usual optimisations, query cache for example.

    best regards
    Last edited by oesxyl; 04-21-2011 at 06:58 PM.


  •  

    Posting Permissions

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