MySql: if value exists UPDATE else INSERT
mysql, php
Solution
How about REPLACE INTO:
REPLACE INTO models
( col1, col2, col3 )
VALUES
( 'foo', 'bar', 'alpha' )
Assuming col1 is your primary key, if a row with the value 'foo' already exists, it will update the other two columns. Otherwise it will insert a new row.
Problem
I have some code that looks like this. There is also an autoincrement field in the table that I must retain (it is used in other tables). I would like to simplify and optimize this code. ``` $query ="SELECT * FROM models WHERE col1 = 'foo'"; $testResult = mysql_query($query) or die('Error, query failed'); if(mysql_fetch_array($testResult) == NULL){ //insert... $query ="INSERT INTO models (col1, col2, col3) VALUES ('foo', 'bar', 'alph')"; $result = mysql_query($query) or die('Error, query failed'); }else{ //update... $query = "UPDATE models SET col1='foo', col2='bar', col3='alph' WHERE col1='foo' AND col2='bar'"; $result = mysql_query($query) or die('Error, query failed'); } ``` Edit: The primary key id is the field that is auto incremented. I never want to alter this. However , when another field(s) is/are duplicated, this is when I want to update that record.