0

I have a query

<cfquery name="qryTemp" datasource="someDSN">
if not exists (select someID from tempTable where someID = 20)
insert into tempTable (someID, colA, colB) values (1,2,3)
else
update tempTable set
colA = 2, colB =3
where someID = 1
</query> 

Is there any identifier which can point out whether it was an insert or update? I can add result attribute to the query if that can help.

CFML_Developer
  • 1,565
  • 7
  • 18
  • Instead of cramming up all the operations in the same `` block, you can split them up in to individual queries. – rrk Jun 13 '18 at 14:25
  • What version of SQL? – Shawn Jun 13 '18 at 16:13
  • 1
    @RRK Using an `UPSERT` methodology is very valid SQL, and it skips the application check and potential extra query to determine whether you want to try an `INSERT` or an `UPDATE`. – Shawn Jun 13 '18 at 16:20

2 Answers2

3

I would try giving the cfquery tag a result argument, and checking if that result has an insert ID:

Edit: used a function that wouldn't crash the <cfif>.

<cfquery name="qryTemp" datasource="someDSN" result="local.results">
if not exists (select someID from tempTable where someID = 20)
insert into tempTable (someID, colA, colB) values (1,2,3)
else
update tempTable set
colA = 2, colB =3
where someID = 1
</query>

<cfif structKeyExists(local.results, 'GENERATEDKEY')>
Do your stuff here...
</cfif>

(Check the Documentation to see which GENERATEDKEY is best for your purpose)

Matthew Moore
  • 866
  • 7
  • 10
  • 1
    Perfect, this will work with a small change. GeneratedKey will not be the part of result struct if it is an update. So I will need to add a validation check for its existence. – CFML_Developer Jun 13 '18 at 14:25
  • @CFML_Developer hah, yes... `isNumeric()` would be a bad call there. I replaced it with `structKeyExists()`. – Matthew Moore Jun 13 '18 at 14:31
  • 1
    @CFML_Developer - You could also just use [MERGE](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017) with `OUTPUT inserted.id, $action AS Action;` The "action" indicates if it was an "insert" or "update". Though out of the box, concurrency is still an issue with both methods http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/ – SOS Jun 13 '18 at 15:57
1

Just to present another option, SQL 2008+ also supports MERGE for "upserts". Adding an OUTPUT clause will provide access to the special $action variable. As the name implies, it will indicate the action actually performed ("insert" or "update").

<cfquery name="qryTemp" datasource="#someDSN#">
    MERGE INTO tempTable tmp
    USING ( VALUES ( 1, 2, 3 )) 
        AS data (someID, colA, colB) 
        ON data.someID = tmp.someID
    WHEN MATCHED THEN
        UPDATE SET tmp.ColA = data.ColA
            , tmp.ColB = data.ColB
    WHEN NOT MATCHED THEN
        INSERT (someID, colA, colB)
        VALUES (data.someID, data.colA, data.colB)
    OUTPUT inserted.someID AS ModifiedID
            , $action AS Action;
</cfquery>

<!--- Demo: Was an insert or update peformed? --->
<cfif qryTemp.Action eq "INSERT">
    ID inserted = <cfoutput>#qryTemp.ModifiedID#</cfoutput>
<cfelse>
    ID updated = <cfoutput>#qryTemp.ModifiedID#</cfoutput>
</cfif>

NB: Though out of the box, concurrency is still an issue with either methods.

SOS
  • 6,430
  • 2
  • 11
  • 29