Portable SQL/Tips/Replacing records

Often you would like to insert a new record or update an existing record if the record already exists.

The usual way

Only MySQL and Oracle provide convenient statements like REPLACE to do this in one step but they are all vendor specific. So many applications use a SELECT statement to decide if an INSERT or UPDATE statement is necessary:

if( exec( "SELECT id FROM table WHERE name = 'test'" ) )
{
  exec( "INSERT INTO table (id, name) VALUES (1, 'test')" );
}
else
{
  exec( "UPDATE table SET name='test' WHERE id = 1" );
}

The alternative

Provided your tables use proper unique indices, there is a simpler way handling this in the application. Instead, of doing a lookup first, always execute the INSERT statement and check for errors. If none has occurred, the new record was inserted successfully. Otherwise, there is already a records with the same ID available and an UPDATE is required instead:

if( exec( "INSERT INTO table (id, name) VALUES (1, 'test')" ) )
{
  exec( "UPDATE table SET name = 'test' WHERE id = 1" );
}

This is also very handy if your used database layer (OpenDBX, PDO) support throwing exceptions in case an error occurred:

try
{
   exec( "INSERT INTO table (id, name) VALUES (1, 'test')" );
}
catch( Exception $e )
{
   exec( "UPDATE table SET name = 'test' WHERE id = 1" );
}