Combination of INSERT and UPDATE?

Possible to insert if doesn't exist, update if does

[s9901470]

Hi

Is there a way to combine INSERT and UPDATE MySQL?

I want to say "INSERT the data if the username doens't exist, or update the existing data if the username already exists"

Any suggestions?

Thank you in advance.

INSERT INTO scores VALUES ('".$uname."',
'".$score."')

[if username doens't exist already]

combined with

UPDATE scores SET score='".$score."' WHERE uname='".$uname."'

[for when username does exist already]

[grandpa]

This is some code I use to check a customer record, and UPDATE if found or INSERT if not found.

First the query, then test the result for the customer:

$result = mysql_query("SELECT * FROM customer_master WHERE Customer_ID = '$_CUSTID' ");
$row = mysql_fetch_row($result);

if ($_CUSTID == $row[1]) {
// CUSTOMER FOUND - UPDATE
$_UPDATEflag = 'ON';
}
else {
$_UPDATEflag = 'OFF';
}

Now you can check the flag, $_UPDATEflag.

if ($_UPDATEflag == 'OFF') {
$sql = "INSERT INTO customer_master (Customer_ID,Customer_Name) VALUES ('$_CUSTID','$_CUSTNM')";
$result = mysql_query($sql);
}
else {
$sql = "UPDATE customer_master SET Customer_Name = '$_CUSTNM',Contact = '$_CONTAC')";
$result = mysql_query($sql);
}

.. you can obviously skip the flag and simply perform the UPDATE or INSERT where the flag is getting set. I'm using the flag as one step of a longer process.

[arran]

If you're using MySQL version 5.0+, you could use a stored procedure to avoid making several database calls.

Something like:

create procedure user_insert_update (
IN uname_in varchar(20),
IN score_in varchar(20),
)
BEGIN
If exists (select 1 from scores where uname = uname_in) then
 update scores set score=score_in where uname=uname_in;
else
 insert into scores values(score_in,uname_in);
end if;
END

[coopster]

With MySQL >= 4.1.0 you can INSERT ... ON DUPLICATE KEY UPDATE [dev.mysql.com]

If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

[grandpa]

Is there a way to combine INSERT and UPDATE MySQL?

It appears there is a way. Choose your medicine.

[arran]

I would go with coopster's neat solution.

[coopster]

If you aren't on MySQL >= 4.1.0 you could also attempt the UPDATE first and then check for any affected rows [php.net]. Works nicely too.