Adding a column and update values using SQL in QGIS

Question

I have a vector file "data.shp" containing the following fields:

ID, value1, AREA

I want to add a new column "value2" and set it as value1/AREA. Since I want to do that for many columns and files I want to use SQL.

I tried with virtual layer in QGIS with no success. I wrote in the query the following :

ALTER TABLE data 
ADD value2 float;
UPDATE data 
SET value2 = value1/AREA

Are alter and update commands working with virtual layer? Is there any other way?

Answer 1

If you want to use just SQL, do it with ogrinfo and -sql paramater.

First command must use the GDAL OGR SQL dialect https://www.gdal.org/ogr_sql.html

ogrinfo -sql "alter table my_shape add column value2 double" my_shape.shp

Second command must use the GDAL SQLite SQL dialect https://www.gdal.org/ogr_sql_sqlite.html

ogrinfo -dialect SQLite -sql "update my_shape set value2=value/(ST_Area(geometry)" my_shape.shp