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?
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?
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?
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,
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.
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.
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.
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.
Before you post, read our: 
i start to think to the problem backwards, starting with existence and i didn't realised that could be useless, 
