SQL to update full text search has a bad query

[iandees]

This line in the full text search update SQL doesn't execute:

census-api/full-text-search/metadata_script.sql

Line 167 in 93305fa

UPDATE search_metadata SET document = document || to_tsvector('simple', coalesce(sub.code, ' ')) WHERE text4 = sub.geoidlower(text2) = '500' AND type = 'profile';

psql:/home/ubuntu/census-api/full-text-search/metadata_script.sql:167: ERROR:  missing FROM-clause entry for table "sub"
LINE 1: ...ment = document || to_tsvector('simple', coalesce(sub.code, ...
                                                             ^

It's not clear to me what sub should be referring to here.

[JoeGermuska]

copy paste error. The correct SQL is:

UPDATE search_metadata 
    SET document = document || to_tsvector('simple', coalesce(subquery.code, ' ')) 
    FROM
        (select regex.geoid, regex.match[2] || '-' || regex.match[1] as code from 
        (select text4 as geoid, regexp_matches(text1, 'Congressional District (\d+), (..)') as match 
         from search_metadata where type = 'profile' and text2 = '500') regex
    ) subquery
    WHERE search_metadata.text4 = subquery.geoid;

[JoeGermuska]

ah, i see that the right SQL is in there too... so really just a c/p error