update if not insert

sonicfusion

I found this code here http://www.pervasive-postgres.com/postgresql/tidbits.asp

UPDATE people SET name = in_name, email=in_email WHERE id = in_id;
IF NOT FOUND THEN
INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email);
END IF;

I realise this is PostgreSQL not MySQL, but i wondered if a similar query would be possible with MySQL [UPDATE...IF NOT FOUND THEN...INSERT].

I would be grateful of any help 🙂

Thank you everyone.

Plasma

REPLACE INTO

A row is considered to exist if there will be a key duplication.

AIS4U

sonicfusion:

To my knowledge there is not any "IF NOT FOUND THEN statement in MYSQL and there does not appear to be any reference to it in the manual at:
http://dev.mysql.com/doc/mysql/

It appears that the in_name, in_email, and in_id are variables representing some data that has been entered into a form by a user. Then when the form is submitted the UPDATE statement runs and if the person's name, email and id are not found, they are added to the database.

This would be something commonly used in a registration form and could be easily duplicated in PHP and MYSQL, but not using your code example.

eriksmoen

simple php would be as follows:

if(@mysql_query("select in_id from people where id='$in_id')")
{
    @mysql_query("UPDATE people SET name='$in_name', email='$in_email' WHERE id='$in_id'");
}
else{
    @mysql_query("INSERT INTO people(id,name,email) VALUES ('$in_id', '$in_name', '$in_email')");
}

... or something along those lines...

eriksmoen

if(!@mysql_query("UPDATE ...."))
{
    INSERT
}

lazzerous

INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email) ON DUPLICAKE KEY UPDATE name='$in_name', email='$in_email';

Roger_Ramjet

MySQL has 2 methods for handling this:
the sql standard way with ON DUPLICATE KEY UPDATE as lazzerous says, and REPLACE - a mysql extension to standard sql.

whoops, sorry Plasma, I did not see you has already answered this as well.