Allow to update single column using aggregate update function without providing whole record set .

Aggregates & Queries
On our radar

Allow to update single column using aggregate update function , instead  record set as input parameter there must be table id and particular column with value which need to be updated

Created on 3 Nov 2017
Comments (8)

You could use an advanced SQL for this though.

Yes Claring i can, but i  like to use aggregate as much as possible in my application.There is performance difference between an aggregate and Advanced SQL.

That's certainly true, still this could only be applied to an existing record. (so for update functionality only) If you already have an existing record you could just get the record and change the 1 column you need to be updated right?

Could you explain what is the benefit here?

What if i dont have existing record  .I don't want  first fetch that record using get function  then update it. Instead of using two aggregate function i would like to use single function .

There's no "performance difference" between an Aggregate and an Advanced SQL, unless you create shitty SQL :).

Kilian -

Does the optimizer apply to Advanced SQL on a SELECT? In other words, if you SELECT a bunch of columns that go unused, will it pull the data anyways? If so... then yes, there is a performance difference (though not in the case of an UPDATE or INSERT statement that only affects one row).

Also, if doing an UPDATE on a *single record*, using the CreateOrUpdateX or UpdateX function built-into OutSystems, by default it only updates the changed rows, so that would be a performance improvement.

That said, the request as-asked-for doesn't seem to have any performance benefits. It would still loop through the list of updates one-by-one and do them as separate statements.



The OP stated "There is performance difference between an aggregate and Advanced SQL." - that's just not true. No, the optimizer unfortunately does not optimize the output of an SQL, but if you select a bunch of unused columns, that's on type of shitty SQL :).

Kilian -

Yeah, in an apples-to-apples comparison (the exact same statement/query), aggregates and advanced SQL are identical. Not sure where that statement would come from.