How do I formulate the on conflict do update-section of this query? When I try set title=q.title, q is unknown. When I try and change 'title' in the select-part to something else and try title=ti I get the message that ti cannot be used in this part of the query.
INSERTINTO wos_2017_1.article (ut, title, author_count)WITH p AS(SELECTARRAY [ ARRAY [ 't','some_namespace' ] ] AS ns),
q AS(SELECT ut,unnest(xpath ('//t:title[@type= "item"]/text()',xml, p.ns))::text title,unnest(xpath ('//t:summary/t:names/@count',xml, p.ns))::TEXT::INTEGERAS author_count FROM p, source.cover_2016)SELECT ut,regexp_replace(regexp_replace(regexp_replace(title,'<','<','g'),'&','&','g'),'>','>','g') title, author_count
FROM q
ONCONFLICT(ut)DOUPDATESET title = title, author_count = author_count;
In the ON CONFLICT... SET we need to use EXCLUDED keyword.
ON CONFLICT (ut) DO UPDATE SET title = EXCLUDED.title, author_count = EXCLUDED.author_count;