asked on

How can I modify this code snippet to check for existing records?

Once this first query has run, how can I have this code check the pmocalendar2 database table if the record exist, and update it, and if the record does not exist, then create a new record?

Here is the code I currently have that queries table1, then writes the records to table2. How can I modify this code?

Thanks
\**************************************************/

<CFQUERY name="QUERY1" dataSource="JadSchedule" dbtype="ODBC" cachedafter="#CreateTimeSpan(0,0,10,0)#">
SELECT JadStartTime, JadEndTime, DocumentType, InitiativeNumber, JadBridge, JadLocation, JadWork, Release, Jad_Date, PhaseNumber, JadStatus, Time_ZoneEN, InitiativeTitle
From System_JAD_Date_Export
</CFQUERY>

<cfloop query="QUERY1">
<cfoutput>
<CFQUERY NAME="QUERY2" DATASOURCE="pmocalendar2">
insert into pmo
(JadStartTime, JadEndTime, DocumentType, InitiativeNumber, JadBridge, JadLocation, JadWork, Release, Jad_Date, PhaseNumber, JadStatus, Time_ZoneEN, InitiativeTitle)

values('#JadStartTime#','#JadEndTime#','#DocumentType#','#InitiativeNumber#','#JadBridge#','#JadLocation#','#JadWork#','#Release#','#Jad_Date#','#PhaseNumber#','#JadStatus#','#Time_ZoneEN#','#InitiativeTitle#')
</cfquery>
</cfoutput>
</cfloop>

ASKER CERTIFIED SOLUTION

cheekycj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

winningl

CJ is right, but not check the query1. You have to check each record in pmo table inside the loop. Here is the code:

<CFQUERY name="QUERY1" dataSource="JadSchedule" dbtype="ODBC" cachedafter="#CreateTimeSpan(0,0,10,0)#">
SELECT JadStartTime, JadEndTime, DocumentType, InitiativeNumber, JadBridge, JadLocation, JadWork,
Release, Jad_Date, PhaseNumber, JadStatus, Time_ZoneEN, InitiativeTitle
From System_JAD_Date_Export
</CFQUERY>

<cfloop query="QUERY1">

<cfoutput>

<cfquery name="exist" datasource="pmocalendar2">
select JadStartTime
from pmo
where Jadstarttime=#JadStartTime# and JadEndTime= #JadEndTime#
</cfquery>

<cfif #exist.recordcount# is 0>

<CFQUERY NAME="QUERY2" DATASOURCE="pmocalendar2">
insert into pmo
(JadStartTime, JadEndTime, DocumentType, InitiativeNumber, JadBridge, JadLocation, JadWork, Release,
Jad_Date, PhaseNumber, JadStatus, Time_ZoneEN, InitiativeTitle)

values('#JadStartTime#','#JadEndTime#','#DocumentType#','#InitiativeNumber#','#JadBridge#','#JadLocation#','#JadWork#','#Release#','#Jad_Date#','#PhaseNumber#','#JadStatus#','#Time_ZoneEN#','#InitiativeTitle#')
</cfquery>

<cfelse>
<CFQUERY NAME="QUERY3" DATASOURCE="pmocalendar2">
update pmo
set whatever
where where Jadstarttime=#JadStartTime# and JadEndTime= #JadEndTime#
</cfquery>

</cfif>
</cfoutput>
</cfloop>

winningl

Moondancer

g118481----> These are your open questions here. Many date back to the year 2000. Help requested, help given and open today.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=coldfusion&qid=11397018
https://www.experts-exchange.com/jsp/qShow.jsp?ta=winnt&qid=20171262
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20166677
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=11811578
https://www.experts-exchange.com/jsp/qShow.jsp?ta=java&qid=20192428
https://www.experts-exchange.com/jsp/qShow.jsp?ta=java&qid=20006788
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=11947538
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=11626838
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=11373117
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20233826
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20182621
https://www.experts-exchange.com/jsp/qShow.jsp?ta=coldfusion&qid=20268689
https://www.experts-exchange.com/jsp/qShow.jsp?ta=coldfusion&qid=20263774
https://www.experts-exchange.com/jsp/qShow.jsp?ta=coldfusion&qid=20256576
https://www.experts-exchange.com/jsp/qShow.jsp?ta=coldfusion&qid=20187192
https://www.experts-exchange.com/jsp/qShow.jsp?ta=coldfusion&qid=20121821

Your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, you must tell the other participants why you wish to do this.

Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process: Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues.

https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

PLEASE DO NOT AWARD THE POINTS TO ME.
_________________________________________
------------> EXPERTS:

Please leave any comments regarding this question here on closing recommendations if this item remains inactive another three days.

Thank you everyone.

Moondancer
Community Support Moderator @ Experts Exchange

P.S. For year 2000 questionS, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.

cheekycj

IMHO, both methods will work.

winning! answer was more complete and mine was simpler :-)

PAQ it or split it. Either way.

CJ

Moondancer

Points have been split, this item closed, thanks CJ.
Points for winningl -> https://www.experts-exchange.com/jsp/qShow.jsp?qid=20289364
Moondancer - EE Moderator

cheekycj

Thank you.