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

The command doesn't know. That's why you need to check the SQLCODE variable.

If SQLCODE  = 0 then the row exists and it was updated. If SQLCODE  = 100 then the row doesn't exist. If it not equal to either then you have a problem.

Example for Embedded SQL:

 &SQL(update WH.Size
   set Height = 1000
 where %ID = 10)
 write SQLCODE

Example for Dynamic SQL:

 SET myquery = "update WH.Size set Height = 1000 where %ID = 10"
 SET tStatement = ##class(%SQL.Statement).%New()
 SET tStatus = tStatement.%Prepare(myquery)
 SET rset = tStatement.%Execute()
 write rset.%SQLCODE

Not sure what you mean by "if the row exists" in regards with insert. The row does not exist, because you're creating it.

The documentation includes a lot of info about INSERT OR UPDATE Sql command, including:

"An existing row is one in which the value being inserted already exists in a column that contains a unique constraint. For more details, see Uniqueness Checks."

"When using INSERT OR UPDATE, you can only insert IDKEY column values, not update them. If the table has an IDKEY index and another UNIQUE constraint, INSERT OR UPDATE matches these columns to determine whether to perform an insert or an update. If the other key constraint fails, this forces INSERT OR UPDATE to perform an update rather than an insert. However, if the specified IDKEY column values do not match the existing IDKEY column values, this update fails and generates an SQLCODE -107 error, because the update is attempting to modify the IDKEY columns."

I suggest to read carefully the relevant documentation page.