[SOLVED] Automatic INSERT vs. UPDATE

[F1Fan]

OK, so I have a frequent need to insert or update data into a table. But the problem is that I have to do a select to determine if it needs to be an update or an insert. This works perfectly, but it's a little bulky.

 

So, my question is this: Is there a way to have ONE query run that inserts if the where statement in the update doesn't return any rows to update?

 

Here's my working code:

<?php
	$query = "SELECT c FROM feature_count WHERE f = '$f' AND a = '$a'";
	$count = $db->getOne($query);
	if ($count>0){
		$count++;
		$query = "UPDATE feature_count SET c = $count WHERE f = '$f' AND a = '$a'";
	}
	else{
		$query = "INSERT INTO feature_count (f, a, c) VALUES ('$f', '$a', 1)";
	}
	$db->query($query);
?>

 

Basically, this function runs on every page someone on my site hits. It counts the features that are used. I really just want something like this:

 

<?php
$query = "UPDATE feature_count SET c = (c+1) WHERE f = '$f' AND a = '$a'";
?>

 

And if there are no rows where f = '$f' AND a = '$a', I want it to insert a row with f = '$f' and a = '$a' and c = 1.

 

Any ideas?

[Mchl]

MySQL has INSERT INTO table VALUES () ON DUPLICATE KEY UPDATE ...  statement. I can't find it in posgresql documentation, but you might give it a try.

[F1Fan]

Thanks, I'll give that a shot.

[F1Fan]

Nope, I can't find anything like that in PosgreSQL, however, I did manage to simplify it to this:

 

<?php
	$query = "UPDATE feature_count SET c = (c+1) WHERE f = '{$c['f']}' AND a = '{$c['a']}'";
	$result = $db->query($query);
	if ($db->affectedRows()==0){
		$query = "INSERT INTO feature_count (f, a, c) VALUES ('{$c['f']}', '{$c['a']}', 1)";
		$result = $db->query($query);
	}
?>

 

Thanks for your reply. It led me in the right direction.

[Mchl]

Two queries instead of three. That's improvement.

[F1Fan]

Exactly!

[btherl]

"UPSERT" (update if exists, insert if doesn't exist) is on the todo list for postgresql, but it doesn't seem to be high priority.

 

You can use triggers to simulate it for specific tables (eg, have your trigger check if the row exists and then insert or update accordingly)

[F1Fan]

Cool. Thanks!