For future reference and everyone tripping over this issue, I am pasting here my working version based on the above accepted answer.

```
DROP TABLE IF EXISTS :schema.tbl_gtfs_stops_de_not_dhid_refine;
CREATE TABLE IF NOT EXISTS :schema.tbl_gtfs_stops_de_not_dhid_refine AS SELECT * FROM :schema.tbl_gtfs_stops_de_not_dhid;
CREATE INDEX IF NOT EXISTS refine_id_idx ON :schema.tbl_gtfs_stops_de_not_dhid_refine(stop_id);
CREATE INDEX IF NOT EXISTS refine_geom_idx ON :schema.tbl_gtfs_stops_de_not_dhid_refine USING GIST (the_geom);
CREATE INDEX IF NOT EXISTS zhv_dhid_idx ON gtfs.stops_zhv(dhid);
CREATE INDEX IF NOT EXISTS zhv_geom_idx ON gtfs.stops_zhv USING GIST (the_geom);
VACUUM ANALYZE;
UPDATE
  :schema.tbl_gtfs_stops_de_not_dhid_refine AS t1
SET
  stop_id = (
    SELECT
      CONCAT_WS(':',t2.dhid, t1.stop_id)
    FROM
      gtfs.stops_zhv AS t2
    ORDER BY
      t1.the_geom <-> t2.the_geom
    LIMIT
      1
  );
```

You can find the code [here](https://git.wtf-eg.de/dancesWithCycles/delfi/src/branch/main/db/sql/refine.sql)