How to tell between update and insert ?

Report · May 27, 2009

I have a form where a user can enter a value, press a button and anoher input would appear, add another value, press the button again, etc. When done, he submits and I use cfloop list to insert the data into a table.

When I bring up this form again, I check to see if there are any values in the table. If they are there, I display the values in a table, one per row. At the end, I have the button again, just in case they need to add more values.

Here is the problem that I am running into and do not know how to solve. How do I know whether they added a new value, or simply changed one of the existing values ? Or what is they did both, change an exisitng value and add a new value ?

This is the code I use if they chagned an exisitng value :

<cfloop index="dateCodeID" list="#form.dateCodeID#" delimiters=",">
<cfquery name="qryUpdateDateCodes" datasource="db">
udate table
set dateCode = '#Evaluate("form.dateCode#dateCodeID#")#'
where stockScreenID = '#form.stockScreenID#'
and dateCodeID = '#dateCodeID#'
</cfquery>
</cfloop>

and I would use the cfloop list to insert a new value, but how would I know which one to use when they submit ? I do not know whether an exisitng value(s) changed or new value(s) added.

Thanks

Report · May 27, 2009

My approach is name the formfields to reflect my intended action. When creating the update stuff, the fields would be called update#id_of_the_record# or something like that. Fields for new records would be called something like NewRecord#something#.

Something else I do for the update part, is to start by putting all the formfield stuff into a cold fusion query. Then I run a db query followed by a Q of Q to identify which records actually need updating. No sense running an update query when the data didn't change.

Report · May 28, 2009

You could put your logic in a stored procedure if your database supports this.

Psuedo code for logic contained in stored procedure:

-- try to update any existing records
UPDATE table
SET dateCode = value1
WHERE stockScreenID = value2
AND dateCodeID = value3

-- if no records updated, insert a new record
IF ( COUNT_OF_RECORDS_UPDATED = 0 )
INSERT INTO table ( dateCode, stockScreenID, dateCodeID )
VALUES ( value1, value2, value3 )

Alternately you could have your stored procedure check to see if a record matching the the stockScreenID and dateCodeID values exists. Then either execute an update or insert.

Report · May 31, 2009

ok, I think I understand what you are dolng here, but I have a couple of questions.

So if I have three existing values displayed, and add a new one, would I first loop thru the insert to check for the three existing records, and then insert the new record if no insert is performed ?

Is the statement IF ( COUNT_OF_RECORDS_UPDATED = 0 ) the actual statement to check for udpated records ?

What if I update only one of the three existing records, and also add a new record ?

Report · Jun 01, 2009

The variable that determines whether it's an update or an insert is the form field that corresponds to the primary key of the database table. With each form submission, check to see if that variable already occurs as a primary key in the table. If so, then update the record corresponding to that key. If not, insert a new record.

Report · Jun 01, 2009

So if I have three existing values displayed, and add a new one, would I first loop thru the insert to check for the three existing records, and then insert the new record if no insert is performed ?

You would loop and call the stored procedure once for each record to be handled. The stored procedure contains the logic to handle the insert or update determined by whether or not a record with the matching primary key value exists in your table. Some databases have built in functionality to handle insert or update logic for you. Microsoft SQL Server 2008 has MERGE ( http://msdn.microsoft.com/en-us/library/bb510625.aspx ) and MySQL has INSERT ON DUPLICATE KEY UPDATE ( http://dev.mysql.com/doc/refman/6.0/en/insert-on-duplicate.html ).

Is the statement IF ( COUNT_OF_RECORDS_UPDATED = 0 ) the actual statement to check for udpated records ?

Yes, this statment is used to determine if the previous statement updated any records in the database. The exact syntax will depend on your database server version.

What if I update only one of the three existing records, and also add a new record ?

I'm not sure I follow you. Perhaps if you could post your ColdFusion and SQL code we could offer better responses. You might also post a SQL specific question in a forum for your database version.

Report · Jun 01, 2009

I'm not sure I follow you. Perhaps if you could post your ColdFusion and SQL code we could offer better responses.

To clarify could you post your input form and table structure? I'd like to see what your primary key(s) for your table are.

Report · May 29, 2009

Update or insert is not determined by how you get data into the database table, but by what you wish to get into the table. The main thing to keep your eye on is the table's primary key.

If a process involves a new primary key, then you must insert a new row. If a process leaves the existing primary keys intact, then you should update.

Report · Jun 02, 2009

MySQL provides a non-standard REPLACE INTO syntax which glosses over this issue.

However... I prefer to use stored procedures and views for all database activities these days, mainly because it puts all of the database-related logic and dependencies into the database itself... where I can always find them and can always know what is actually going on. Put the necessary logic, whatever it is, into the stored procedure.

Report · Jun 02, 2009

You can do the insert when necessary and
always do the update even if it is not necessary.

<cfloop index="dateCodeID" list="#form.dateCodeID#" delimiters=",">
<cfquery name="qryInsertDateCodes" datasource="db">
insert into table a
       (a.stockScreenId,       a.dateCodeID,   a.dateCode)
values ('#form.stockScreenID#','#dateCodeID#','#Evaluate("form.dateCode#dateCodeID#")# ')
where ('#form.stockScreenID#','#dateCodeID#') not in
(select b.stockScreenId,       b.dateCodeID
from   table b where b.stockScreenId = '#form.stockScreenID#'
                and   b.dateCodeID    = '#dateCodeID#'
)
</cfquery>
<cfquery name="qryUpdateDateCodes" datasource="db">
update table
     set dateCode = '#Evaluate("form.dateCode#dateCodeID#")#'
    where stockScreenID = '#form.stockScreenID#'
and dateCodeID = '#dateCodeID#'
</cfquery>
</cfloop>

Message was edited by: draves

Report · Jun 04, 2009

Thanks draves for the response and code sample, I think this is exactly what I am looking for.

One question. If there is no new record to to insert, it would go thru the insert process anyways, and the NOT IN part will fail, so there would be no insert ? Then it will just fall thru and always perform the update even if there were no changes ?

Report · Jun 04, 2009

Yes, only if the data is not in a record will a new record be inserted and the update will always happen.

Report · Jun 04, 2009

Another approach is to insert everything into a working table. Then do your inserts and updates using sql.