UPDATE with WITH and CASE - PostgreSQL

Question

I'm trying to change the values of a column to be a title constructed from info from two other tables, however I'm running into trouble getting the data in. I currently want to execute this query on all entries to the table. I'm getting a syntax error on CASE and I can't figure out why.

UPDATE campaigns AS cmp     SET name = (         WITH ptn AS (SELECT first_name, last_name FROM politicians WHERE id = cmp.politician_id),             rc AS (SELECT office FROM races WHERE id = cmp.race_id)          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     ) 

This is PostGres 9.4. Here's the error I'm getting

ERROR:  syntax error at or near "case" LINE 5:   case           ^  ********** Error **********  ERROR: syntax error at or near "case" SQL state: 42601 Character: 189 

Answer 1

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 

Answer 2

I would be inclined to do this with from clause:

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 ;