[Qgis-user] automating the updating of records in existing layer using values from a joined layer

[Janneke van Dijk]

Dear all,

I will be getting data from the field (geopackage files) on a regular 
basis and need to join them to the 'masterfile' and update a few column 
values based on the joined attribute values. This works fine manually, 
either by using the field calculator or the update bar with the table in 
editing mode. I would like to automate parts of this process and am 
getting stuck in different attempts.

1. In the model builder, I cannot figure out how to make the field 
calculator take only the selected records to be updated, and I cannot 
figure out how to make it update records in the existing layer as 
opposed to creating a new layer. Is this even possible?

2. In DB manager, I can select the joined masterfile (with fieldwork 
file) as a virtual layer (selects 174 rows, including the joined attributes)

select * from masterfile
where fld_date_updated > '2019-07-19'

but I get a syntax error in the update statement:

update masterfile set result_account = fld_result_account where 
fld_date_updated > '2019-07-19'

"Query execution error on CREATE TEMP VIEW _tview AS update masterfile 
set result_account = fld_result_account where fld_date_updated > 
'2019-07-19': 1 - near "update": syntax error"

I can not find the syntax error. Is this because it's a virtual layer 
and it cannot be updated? Is there a way to update records in virtual 
layers?

3. In DB manager, if I make a connection under geopackage with the 
master file, I am not able to make a join with the virtual layer with 
the field data, it will not recognise the field layer.

I am a bit baffled, as I would think this should be something 
straightforward. Is what I want possible, using either the model builder 
or the db manager and a reusable sql script? Or is it not possible to 
semi-automatically update records from within QGIS without having to 
create a new output file?

Using QGIS 3.8.1 (osgeo installer) 64 bit on windows 10


Thanks for any insights,

Janneke

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20190723/7e7fbd35/attachment.html>