Ewan Whyte · Aug 3, 2020


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 ( 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
      SELECT customerID
      FROM BAB_Production.CustomerRecords
      WHERE :customerID = customerID)
:customerID is extracted from HL7 within DTL



2 0 3 159


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.