0

I have a data loading from an excel file where i am adding all the checks to check for the uploaded file and insert data accordingly, till this point everything seems to be working good.

Now i am using the folowing to insert into a query

<cfquery datasource="dsn">
        INSERT INTO tblcustomers(FirstName,LastName,address,emailaddress,status) 
        VALUES(<cfqueryparam cfsqltype="varchar" value="#queryData.firstname[currentRow]#">,
            <cfqueryparam cfsqltype="varchar" value="#queryData.lastname[currentRow]#">,
            <cfqueryparam cfsqltype="varchar" value="#queryData.address[currentRow]#">,
            <cfqueryparam cfsqltype="varchar" value="#queryData.emailaddress[currentRow]#">,
            <cfqueryparam cfsqltype="integer" value="#queryData.status[currentRow]#">)
        </cfquery>

my focus here is, i can reupload the same file with some minor changes and rather than inserting the data, i should update the existing data if exists and insert if nits new

the unique key is the email here

so if person removes the emailadress from excel and replace with something else, i am bit lost here should i delete that record or insert a new one because email was changed

Neil
  • 23
  • 3
  • Check out https://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par for a useful feature affectionately called UPSERT. – Sam M Feb 11 '21 at 04:05
  • See the manual under INSERT. – Strawberry Feb 11 '21 at 07:21
  • If you are using email as a unique key, how exactly would you know that someone changed their email address? – Dan Bracuk Feb 11 '21 at 14:53
  • exactly, i asked for a suggestion here in my question, i don't know yet – Neil Feb 11 '21 at 14:54
  • In that case, insert the new record and send an email to the new address asking if the two email addresses are for the same person. – Dan Bracuk Feb 11 '21 at 15:56

0 Answers0