INSERT OR UPDATE SQL

Question

Question

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 (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

Robert Cemper · Aug 3, 2020

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

1 0

score 1 · Answer 1 · 2020-08-03T17:59:39-04:00

https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_INSERTORUPDATE
The article is pretty explicit on the issues of IDkey.

score 0 · Answer 2 · 2020-08-06T09:04:56-04:00

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.