UPDATE with WITH and CASE – PostgreSQL

by

The syntax error occurs because your co-related subquery isn’t valid. You need to have some select statement after the two common table expressions:

The basic structure of a common table expression is this: 

with ptn as (...),
  rc as (...)
select --<< you are missing this select here

But I think the whole thing can be written shorter and more efficiently (if I’m not mistaken)

UPDATE campaigns AS cmp
    SET name = CASE
                 WHEN rc.office IS NULL OR rc.office="" THEN ptn.first_name || ' ' || ptn.last_name
                ELSE ptn.first_name || ' ' || ptn.last_name || ' for ' || rc.office
              END
from politicians ptn, races rc 
where ptn.id = cmp.politician_id
  and rc.id = cmp.race_id

Leave a Comment