INSERT vs. UPDATE

[mlse]

Here's a little chore that I ran across last time I used MySQL for anything serious (a while back!) and I wonder if there's a neat way to do it in modern versions of MySQL.

It goes like this:

I have some data that I wish to insert into a table if it is not already in the table (according to some constraint) or update the row that matches the constraint with the new data (Note that this is different from REPLACE because no unique/primary key is required.).

Previously, I've done it using 3 separate queries and a bit of PHP. Here is an example of what I mean, where doquery() is a function written by me which puts the result of the query (if any) into a 2D array (for ease of manipulation):


$constraint = 'foo="bar" AND fruit="apple" AND ... etc'; //This constraint may or may not refer to unique rows and mytable may not contain any unique rows at all.
$data = 'a=1, b=2, c=3, ... etc';
$count = doquery('SELECT count(0) FROM mytable WHERE '.$constraint);
$count = $count[0][0]; //Result is obviously in a single-celled array.

if ($count > 0) //The row exists according to the constraint so update it.
doquery('UPDATE mytable SET '.$data.' WHERE '.$constraint);
else //The row doesn't exist so create it.
doquery("INSERT INTO mytable SET '.$data);


How can I do this in a single MySQL statement instead of using a PHP hack?

I must confess that my approach to MySQL until now has been "If I don't know how to do it in MySQL alone, sod it, just write a PHP hack!". This has been OK for small databases, but I know it won't be for big ones.

[CFMaBiSmAd]

The only built-in way is to use an INSERT ... ON DUPLICATE KEY UPDATE ... -

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

[mlse]

I see how that works! Thanks :)