returning() support for update (DELETED and INSERTED)

[MordantWastrel]

Looking at returning(), it only works on inserts and upserts.

SQL Server lets you do output DELETED.* or output INSERTED.* if you don't want to have to make two queries to see what the value was prior to your update.

It looks like compileUpdate() just needs a small change to look at returning but that returning() filters for column names and so would need a prefix of deleted or inserted to know which you wanted returned (or else just default to deleted since you presumably have the value that was updated in hand already).

reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

[JamoCA]

I'll have to look at the output clause. It looks like it returns a query of affected items prior to the action being performed.

I was encountering issues with native ACF not consistently returning the number of updated rows and have been using the following method to return the data.

q = queryexecute("SET NOCOUNT ON
UPDATE myDatabase
SET columnToUpdate = :newValue
WHERE myID = :id
SELECT rowsUpdated = @@ROWCOUNT
SET NOCOUNT OFF", {
    "newValue": arguments.newValue
    ,"id": arguments.id
});

recordsUpdated = qry.rowsUpdated;

Is this the best approach to getting a record count of affected records? (I believe it requires using raw with QB.) Is there a QB approach to doing this w/SQL Server?

[MordantWastrel]

In SQL Server, you can do this:

var updateReq = queryExecute(
            "UPDATE users SET licenseLevel = :license, licenseLevelCode = :licenseCode OUTPUT deleted.licenseLevel, deleted.licenseLevelCode WHERE stackRecordKey = :AYSOID",
            { license : rc.license_level, licenseCode : rc.license_level_code, AYSOID : rc.cust_guid }
        );

Then your query object (updateReq here) will have licenseLevel and licenseLevelCode.

QB supports this natively on INSERT but not on update. The relevant code in QueryBuilder.cfc doesn't lookin at returning when compiling an update clause, and the one non-trivial change to QueryBuilder.cfc is to prepend deleted or inserted because QB wants to make sure it's putting in column names and will ignore those prefixes if you try to just introspect returning when compiling an update.