Adding file info using ogrinfo -sql UPDATE expression

Question

I try to add the filename with the tool ogrinfo in a bunch of shape files for later merging reasons under Debian 8 and gdal 2.4.0. I stuck by expressing a valid statement for UPDATE part. For the command:

 ogrinfo block1.all.shp \
 -sql "UPDATE TABLE block1.all SET filename='block1.all'"

I always get the error:

layer names ignored in combination with -sql. ERROR 1: SQL Expression Parsing Error: syntax error, unexpected identifier, expecting SELECT or '('. Occurred around : UPDATE

This is strange because the table manipulation part:

 for f in `ls *.shp`; do \
   t=`basename $f .shp`;  \
   ogrinfo $f -sql \
   "ALTER TABLE $t ADD COLUMN filename character(32)"; \
 done

which means expanded for the first file:

 ogrinfo part1.all.shp -sql \
   "ALTER TABLE part1.all ADD COLUMN filename character(32)";

finished without an error.

INFO: Open of `block1.all.shp'
      using driver `ESRI Shapefile' successful.
INFO: Open of `block1.part01.shp'
      using driver `ESRI Shapefile' successful.
...

How do I have to express the matter correctly.

Remark 1: The phenomenon persists also with the SQL conform table-name-convention, suggested by @MichaelStimson.

$ ogrinfo block1_all.shp \
 -sql "alter table block1_all add column block_name varchar(32)";

> INFO: Open of `block1_all.shp'
      using driver `ESRI Shapefile' successful.

$ ogrinfo block1_all.shp \
  -sql "UPDATE TABLE block1_all set filename = 'block1_all'"

> INFO: Open of `block1_all.shp'
      using driver `ESRI Shapefile' successful.
  ERROR 1: SQL Expression Parsing Error: syntax error, unexpected 
  identifier, expecting SELECT or '('. Occurred around :
  UPDATE TABLE block1_all set filename = '

Remark 2:

According to the answer from @user30184 the is here the complete workflow. The start point is a set of some unattributed DXF files with the dot naming convention:

$ ls -1

> block1.all.dxf block1.part01.dxf block1.part02.dxf \
  block1.part03.dxf block1.part04.dxf block1.part05.dxf ...

The mining script:

#!/usr/bin/env bash

# In my case I've a lot of INFO clutter from the DXF source
$skip_dxf_atts=1

# Resulting shape directory for the shapes
$dst_path=shapes

# --- Raw data import part ---
# Iterate over the DXF files
for file in `ls *.dxf`
do

     # Remove the file extension
     dot_name=`basename $file .dxf`

     # Remove the dot from the file name (usc under score)
     usc_name=`echo $dot_name | sed -e 's/\./_/g'`

     # Create the shape file with SRS
     ogr2ogr -s_srs EPSG:32632 \
             -t_srs EPSG:32632 \
             -f 'ESRI Shapefile' \
             shapes/$usc_name.shp \ 
             $file
done
  
# --- Table confection part ----
# Create the target path if exists  
if [ ! -e $dst_path ]  
then
    mkdir $dst_path
fi

# Change to the shape files
cd $dst_path

# Loop over the shapes 
for file in `ls *.shp`
do  
    # Get the table name 
    table=`basename $file .shp`
    
    # Parse the block part
    block=`echo $table | cut -d '_' -f 1`
    
    # Parse the name part
    name=`echo $table | cut -d '_' -f 2`
    
    # Control messages
    echo "FILE:  $file"
    echo "TABLE: $table"
    echo "BLOCK: $block"
    echo "NAME:  $name"
    
    #  Remove the DXF attributes not containing any info
    if [ $skip_dxf_atts == 1 ]
    then
        ogrinfo $file -sql "alter table $table drop column Layer"
        ogrinfo $file -sql "alter table $table drop column PaperSpace"
        ogrinfo $file -sql "alter table $table drop column SubClasses"
        ogrinfo $file -sql "alter table $table drop column LineType"
        ogrinfo $file -sql "alter table $table drop column EntityHand"
        ogrinfo $file -sql "alter table $table drop column Text"
    fi
    
    #  Add the fields block and name 
    ogrinfo $file -sql "alter table $table add column block varchar(16)"
    ogrinfo $file -sql "alter table $table add column name varchar(16)"
    
    #  Fill the fields block and name 
    ogrinfo -dialect sqlite $file \
            -sql "UPDATE $table SET block= '$block' ,name= '$name'"
done

# I'd like to have the envelopes for all blocks apart in on file

# --- Block 1 part ---------------
# Create the initial file from the 
# complete envelope block1_all
ogr2ogr -f 'ESRI Shapefile' \
           block1_envelope.shp \
           block1_all.shp

# Merge the files for block1 together           
for file in `ls block1_part??.shp`
do
   ogr2ogr -f 'ESRI Shapefile' \
   -update -append block1_envelope.shp $file \
   -nln block1_envelope
 done

# --- Block 2 part ---------------
# Create the initial file from the 
# complete envelope block2_all
ogr2ogr -f 'ESRI Shapefile' \
           block2_envelope.shp \
           block2_all.shp

# Merge the files for block1 together           
for file in `ls block2_part??.shp`
do
   ogr2ogr -f 'ESRI Shapefile' \
   -update -append block2_envelope.shp $file \
   -nln block2_envelope
done

# Put the envelopes together
ogr2ogr -f 'ESRI Shapefile' \
           block_envelopes.shp \
           block1_envelope.shp

ogr2ogr -f 'ESRI Shapefile' \
   -update -append block_envelopes.shp \
    block2_envelope.shp \
   -nln block_envelopes

Answer 1

GDAL supports two SQL dialects which can be used for all data sources. In addition to those two for some databases (Oracle, PostgeSQL etc.) the native SQL can be utilized.

The OGR SQL dialect https://gdal.org/user/ogr_sql_dialect.html is made by the GDAL project and it is not at all a full featured SQL language. The other SQL dialect https://gdal.org/user/sql_sqlite_dialect.html is utilizing SQLite and the SpatiaLite extension to SQLite.

In your use case you must use both dialects. OGR SQL is needed for altering the table

ogrinfo -sql "alter table foo add filename text" foo.shp

Because OGR SQL does not support UPDATE, as the error message tries to tell, you must switch to SQLite dialect

ogrinfo -dialect sqlite -sql "update foo set filename='bar'" foo.shp

Check:

ogrinfo -al foo.shp
INFO: Open of `foo.shp'
      using driver `ESRI Shapefile' successful.

Layer name: foo
Metadata:
  DBF_DATE_LAST_UPDATE=2020-01-10
Geometry: Polygon
Feature Count: 1
Extent: (-87.666868, 41.707976) - (-87.666683, 41.708064)
Layer SRS WKT:
(unknown)
FID: Integer64 (11.0)
filename: String (80.0)
OGRFeature(foo):0
  FID (Integer64) = 0
  **filename (String) = bar**
  POLYGON ((-87.666731 41.707976,

Be aware that GDAL SQLite dialect may also have problems with some layer/column names which are valid in SQL. Underscore may belong to problematic characters. Test with simple names. Some problems with names can be avoided by using double quetes around the names. Quotes must be escaped. On Windows the syntax is

-sql "update \"foo\" set \"filename\"='bar'"

If you have dots in names then you must use double quotes because in SQL block1.all means either "table 'all' in schema 'block1'" or "column 'all' of table 'block1'".