Portable SQL/Tips/Replacing records - Revision history

Nose: removed backlink

2008-06-22T13:09:34Z

removed backlink

Nose: introduction

2008-04-05T12:46:01Z

introduction

Nose: /* Conclusion */

2008-04-05T12:42:28Z

‎Conclusion

Nose: performance & conclusion

2007-08-18T21:34:50Z

performance & conclusion

Nose: Replacing records

2007-08-18T17:21:21Z

Replacing records

New page

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]], [http://php.net/pdo 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" );
}

← Older revision		Revision as of 13:09, 22 June 2008
Line 67:		Line 67:

	Always executing the INSERT statement before doing an UPDATE if necessary simplifies the application logic by reducing the number of required statements. Furthermore, it increases the performance if not all records are already available and in the other case the performance is the same as in the traditional approach.		Always executing the INSERT statement before doing an UPDATE if necessary simplifies the application logic by reducing the number of required statements. Furthermore, it increases the performance if not all records are already available and in the other case the performance is the same as in the traditional approach.
−
−
−	~~----~~
−	~~Back to [[Portable SQL\|Overview]]~~

← Older revision		Revision as of 12:46, 5 April 2008
Line 1:		Line 1:
−	Often you would like to insert a new record or update an existing record if the record already exists.	+	__TOC__
		+
		+	Often you would like to insert a new record or update an existing record if the record already exists. If you are able to add an unique index to the column whose values should only exist once, there's an faster alternative to to often used SELECT, INSERT/UPDATE combination.

	== The traditional way ==		== The traditional way ==

@@ Line 64: / Line 64: @@
 == Conclusion ==
-Always executing the INSERT statement before doing an UPDATE if necessary simplifies the application logic by reducing the number of required statements. Furthermore, it increases the performance if not all records are already available. In this case the performance is the same as in the traditional approach.
+Always executing the INSERT statement before doing an UPDATE if necessary simplifies the application logic by reducing the number of required statements. Furthermore, it increases the performance if not all records are already available and in the other case the performance is the same as in the traditional approach.
 ----
 Back to [[Portable SQL|Overview]]

@@ Line 1: / Line 1: @@
 Often you would like to insert a new record or update an existing record if the record already exists.
-== The usual way ==
+== The traditional 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:
@@ Line 33: / Line 33: @@
      exec( "UPDATE ''table'' SET ''name'' = 'test' WHERE ''id'' = 1" );
+  }