INSERT OR UPDATE SQL

Primary tabs

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

Replies

I see 4 critical points to check:

  • if customerID is autogenerated or calculated you can't  insert to it or update it
  • if customerName has some constraints like UNIQUE or fails some other formal checking
  • if the (existing) record is locked by some application running in parallel
  • if some access rights block you  

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.