[perleo]

Can mysql add a record if the update query fails?

Heres what I have. I want to do an update of data on record with a id of X, so I do this..

Code:

 UPDATE table SET val = '5' WHERE id = '1'

but what if id = 1 does not exist? Can i put something into that query that will create that entry if it doesnt exist ?

[Keith]

You could do:

PHP Code:

<?php



$count = mysql_num_rows(mysql_query("SELECT id FROM table WHERE id = 1"));



if ($count == 1)

mysql_query("UPDATE table SET val = '5' WHERE id = 1");

else

mysql_query("INSERT INTO table...");



?>

[YourPHPPro]

Originally Posted by perleo

Can mysql add a record if the update query fails?

What version of mySQL are you using ? If you are using mySQL 5.x you should check out triggers:
http://www.onlamp.com/pub/a/onlamp/2.../triggers.html
http://forums.mysql.com/list.php?99

[dennispopel]

MySQL has a neat feature, but to be used with care:

REPLACE INTO table(id, field1, field2, ...) VALUES(1, f1, f2, ...)

This needs id to be primary key column. If MySQL finds the record with id=1 it will do two things:
1. DELETE the row
2. INSERT the row with the same id.

What to be noted - of you don't specify ALL fields then you will lose existing fields in that row. In short, this mean that this command does not update listed fields, rather it will delete the row and insert fields which have been specified in the command.