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?

• geozelot: Are both 'layers' in PostgreSQL? If so, run an UPDATE through the DB Manager (or the DB client of your choice). If not, import the other first - you can do that through QGIS via drag'n'drop in the Browser panel.
• CruiseLee: @geozelot: what do you mean with drag'n'drop in the Browser panel? How to do that?
• Bera: Drag and drop it: imgur.com/a/2yZYr3P

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?

enter image description here

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.