<!--- Create a query to hold our insert or update data. --->
<cfset qContact = QueryNew(
	"id, first_name, last_name, age",
	"integer, varchar, varchar, integer"
	) />
 
 
<!--- Add a row to the update data. --->
<cfset QueryAddRow( qContact ) />
 
<!---
	Update new row cells. NOTICE that I am only setting values
	for ID, first name, and last name; I am leaving the AGE
	attribute blank. That is because this is an UPDATE data set,
	not an INSERT data in which the data would all be required.
--->
<cfset qContact[ "id" ][ 1 ] = JavaCast(
	"int",
	4
	) />
 
<cfset qContact[ "first_name" ][ 1 ] = JavaCast(
	"string",
	"Anna"
	) />
 
<cfset qContact[ "last_name" ][ 1 ] = JavaCast(
	"string",
	"Banana"
	) />
 
<!--- !! LEAVING AGE FIELD NULL !! --->
 
 
 
<!--- Update the database. --->
<cfquery name="qUpdate" datasource="#REQUEST.DSN.Source#">
	DECLARE @contact TABLE (
		id INT,
		first_name VARCHAR( 30 ),
		last_name VARCHAR( 30 ),
		age INT
	);
 
	DECLARE @id INT ;
 
 
	<!---
		Put one conact in our table.
 
		NOTE: This table would not have to be populated
		ordinarilly. I am only doing this so that the
		example will work. Normally, this contact table
		(or whatever table I was targetting), would be a
		concrete table.
	--->
	INSERT INTO @contact (
		id,
		first_name
	)(
		SELECT
			4,
			'Anna'
	);
 
 
	<!---
		Set the internal which we are going to use to see
		if we are updating or inserting a record.
	--->
	SET @id = ISNULL(
		(
			SELECT
				id
			FROM
				@contact
			WHERE
				id = <cfqueryparam value="#qContact.id#" cfsqltype="cf_sql_integer" />
		),
		0
	);
 
 
 
	<!--- Check to see if the ID was found or not. --->
	IF (@id = 0)
		BEGIN
 
			<!---
				The ID we have in our incoming data did NOT
				correspond to an ID existing in our database;
				therefore, we need to insert a new contact.
			--->
			INSERT INTO @contact
			(
				id,
				first_name,
				last_name,
				age
			) VALUES (
				<cfqueryparam value="#qContact.id#" cfsqltype="cf_sql_integer" />,
				<cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#qContact.age#" cfsqltype="cf_sql_integer" />
			);
 
			<!--- Store the new ID. --->
			SET @id = @@Identity;
 
		END
	ELSE
		BEGIN
 
			<!---
				The ID we have in our incoming data DID
				correspond to an ID in the existing dataase;
				therefore, we are just going to update the row
				based on the available data in the query.
			--->
			UPDATE
				@contact
			SET
				<!--- Check for first name data. --->
				<cfif Len( qContact.first_name )>
 
					first_name = <cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />,
 
				</cfif>
 
				<!--- Check for last name data. --->
				<cfif Len( qContact.last_name )>
 
					last_name = <cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />,
 
				</cfif>
 
				<!--- Check for age data. --->
				<cfif IsNumeric( qContact.age )>
 
					age = <cfqueryparam value="#qContact.age#" cfsqltype="cf_sql_integer" />,
 
				</cfif>
 
				id = @id
			WHERE
				id = @id
			;
 
		END
	;
</cfquery>