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

By Rocky on 3 Nov 2017

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

Claring3 Nov 2017

You could use an advanced SQL for this though.

Rocky3 Nov 2017

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.

Claring3 Nov 2017

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?

Rocky3 Nov 2017

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 .

Kilian Hekhuis3 Nov 2017

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

Justin James5 Nov 2017

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.


Kilian Hekhuis6 Nov 2017


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 :).

Justin James6 Nov 2017

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.