Update fields in table in PostGIS database by joining attribute by field value from a layer

Question

I have a table in my PostGIS database. Its geometric objects I loaded in to my QGIS project as layer where I can edit them. In this layer (layer A) there are features with null values in some fields. I have another layer B with features containing specific values in these fields. I like to update the layer A with the values of the layer B by joining the segment_id.

With the tool "Join attributes by field value" from the toolbox I do not have the option to update an existing table. I can just save the output as a new table in my database.

Is there another way to update a table in the database with values from the attributes of a layer?

Answer 1

Import table B to PostgreSQL and:

UPDATE test.table_a AS a
SET 
    attr1 = b.attr2,
    attr2 = b.attr2,
    attr3 = b.attr3
FROM test.table_b as b
WHERE a.segment_id = b.segment_id

See: How to do an update + join in PostgreSQL?

Answer 2

You could join layer B with layer A on segment_id (layerproperties -> joins). Then you can open the attributetable and sort the field with missing values in table A. Then select all rows with missing values and use the fieldcalculator (start editing and open the combobox in the attributetable to select the field you want to update, then click on the E symbol) In the fieldcalculator choose fields and values and apply to selected rows.