In the current LTR of QGIS - 3.14 is it possible to directly update a column using SQL?

I understand in previous questions the advice was to use OGRINFO - Adding a column and update values using SQL in QGIS

Is there a way to run something like

Column_to_update = 
{Select max(End_Chainage)
from DSC_Roads
group by RoadMntnc}

Currently the only way I can think of doing this is to run it (as below) in DB Manager, join by a key field and then update the column. Is there a single step to do the same?

enter image description here

  • 3
    You cannot use the SQL syntax, but you can create such an expression in field calculator using aggregate functions. – Zoltan Jul 31 '20 at 9:45
  • 2
    I stick to having data in a database, not file, and doing updates from a sql editor available for the database. From sql in db you can notify a redraw of the layer in qgis. – Jakob Jul 31 '20 at 11:32
  • Are you using shapefiles as your data source? – DPSSpatial_BoycottingGISSE Jul 31 '20 at 14:43
  • @DPSSpatial yes the data is in shp – GeorgeC Aug 2 '20 at 22:04
  • @Zoltan I tried aggregate(layer:='DSC_Roads',aggregate:='max',expression:="End_Chainage") but can't figure out how to group it by RoadMntnc which is unique. So the max value should be from within each Road and not all roads. – GeorgeC Aug 2 '20 at 22:48

Here is an example with a linestring layer (Hungarian rivers) having two attributes, "len" - the length of the feature and "NEV" - the name of the feature. Features are between two junctions, so the same name may be used several times. Using the Field calculator I created a virtual column with maximal length of features with the same name:

field calculator expression

Then you can see in the attribute table:

result table

I usually prefer virtual column for calculated values. The virtual columns are updated when you open the attribute table. Permanent columns must be updated manually.

  • So simple...thanks. maximum("End_Chainage","RoadMntnc") – GeorgeC Aug 3 '20 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.