Question
· Aug 3, 2020

INSERT OR UPDATE SQL

Hi, I want to INSERT a customerID and customerName to a table if the customerID does not exist. Or UPDATE if they've changed their name.
I've reviewed the documentation (https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=RS...) and have confused myself with the INSERT OR UPDATE statement. Rather than using two individual statements , is there a way to get it in one?

What I have at the moment doesn't do anything. In fact it errors.

I intend to use this within a code block in a DTL.

 

 set customerName = ##class(BAB.Utils.ExternalSystem.TransformFunctions).CustomerRecord(customerID)

     &sql(INSERT OR UPDATE BAB_Production.CustomerRecords
     (customerID,:customerName)
      SELECT customerID
      FROM BAB_Production.CustomerRecords
      WHERE :customerID = customerID)
      
       
:customerID is extracted from HL7 within DTL

 

Thanks

Discussion (3)1
Log in or sign up to continue

Thanks for coming back to me Robert, I ended up resorting to using a count to make make my decision for INSERT or UPDATE as I couldn't get the method to work. 

I think that the document you mentioned and that was the same one I quoted. Perhaps I'll just stick to the classic methods rather than using INSERT OR UPDATE.

Thank you.