updateOrInsert should return "generatedKey" somehow, even on updates...

[Daemach]

I love the update or insert, but really need a key back, either the inserted key or actual key from the updated record (my where clause checks a fk).

The documentation is ambiguous. If the following is how it actually works, then perhaps adding one more argument to updateOrInsert specifying idField = "id" would work. If that argument exists, assume I want a key and fall back to using whereIn, limit 1 instead of exists and return the id field, setting result.generatedKey - this would be non-performant, but I accept the hit in this situation.

Performs an update statement if the configured query returns true for exists. Otherwise, performs an insert statement.

If the following is how it actually works, then it would be even less performant, but still immensely helpful

If the configured query returns 0 records, then an insert statement is performed.

[Daemach]

FWIW, this is how I get around it now:

var aid = qb.newQuery()
                    .from("accounts")
                    .where("portfolioCode", {value: q.portfolioCode,cfsqltype="CF_SQL_VARCHAR"})
                    .updateOrinsert({
                      apxPortfolioID: q.PortfolioID,
                      portfolioCode: q.portfolioCode,
                      taxID: q.TaxNumber,
                      bTaxable: (q.TaxStatus == "Taxable"),
                      name: q.ReportHeading1,
                      shortName: q.shortName,
                      ownerContactID: contact(q.OwnerContactID),
                      primaryContactID: contact(q.primaryContactID),
                      physicalAddressID: address(q.OwnerAddressID),
                      accountScreens: q.ReportHeading3,
                      bRetail: (q.PortfolioTypeCode != "Institutional")? 1 : 0,
                      benchmark: getBenchmarkID(q.portfolioIndex)
                    })

        aid = (aid.result.keyexists('generatedKey')) ? aid.result.generatedKey : qb.newQuery().from("accounts").where('portfolioCode', {value: q.portfolioCode,cfsqltype="CF_SQL_VARCHAR"}).value('id');

[elpete]

Your suggestion of asking for the first record's id would work to get it back but it would no longer return a qb result object. (The qb result object is the object with both result and query on it you are using on the last line of your example.) One potential option would be to add a returning call to your query and specify the id there.

I'm fine to go with your suggestion as long as the return value follows the other return values for update calls.

[Daemach]

It seems like one could still return a qb result object, though it would require adding the key/value to that object. That being said, you're more familiar with what's going on under the hood.

I have never used the returning call - not sure how that would work.

[Daemach]

Something like this may solve this problem. Not sure which dbs support this kind of thing though: https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

[elpete]

The problem I'm trying to solve before implementing this is how we know what the generatedKey column would be when we fetch the record.

For instance, with either an update or a select, we don't know based off the return value what the primary key of the table is, so we don't know what to return.

You may be able to use upsert for this functionality, but I'm not sure if Adobe or Lucee return the generatedKey value for SQL Server or Oracle when using MERGE. (I know they do when using MySQL and Postgres because they use INSERT statements for upserts.)