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
Cheers everyone helping me to complete this!