Written by

Deloitte
Question Michael Wood · Jan 19, 2024

INSERT OR UPDATE only inserts

I am trying to do a INSERT OR UPDATE SQL query, but the query always inserts new rows to table. Is it how I defined the table or am I mising something in the query?

table defines as,

Class AH.AHCOMMON.Tables.HospDepartmentData Extends %Persistent [ Language = objectscript ]
{

Property Tablespace As %Library.String(MAXLEN = 50) [ SqlColumnNumber = 2 ];

Property DepartmentId As %Library.String(MAXLEN = 50) [ SqlColumnNumber = 3 ];

Property TimezoneName As %Library.String(MAXLEN = 100) [ SqlColumnNumber = 4 ];

Property PatientDepartmentName As %Library.String(MAXLEN = 100) [ SqlColumnNumber = 5 ];

Property Status As %Library.String(MAXLEN = 50) [ SqlColumnNumber = 6 ];

Property CreatedDateTime As %DateTime [ SqlColumnNumber = 7, SqlComputeCode = {set {CreatedDateTime}=$ZDT($H,3)}, SqlComputed, SqlComputeOnChange = %%INSERT ];

Property UpdatedDateTime As %DateTime [ SqlColumnNumber = 8, SqlComputeCode = {set {UpdatedDateTime}=$ZDT($H,3)}, SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

Product version: IRIS 2021.2

Comments

Enrico Parisi · Jan 19, 2024

Hi Michael,

this has been discussed in your previous question "INSERT OR UPDATE" here.

Did you check my answer there?

Does the class shown above (looks truncated) has a column that contains a unique constraint?

Can you provide the rest of your class, in particular index definitions and the INSERT OR UPDATE statement you are using?

0