How to tell between update and insert ?

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

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

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.

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.

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.

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.