UPDATE with WITH and CASE - PostgreSQL

39
votes

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
2
sqlpostgresql
Added the error to the post – cfatt10
Please edit into your question. Also your exact postgresql version tag. Please read about asking questions in the help center. – philipxy
I think the CTE's have to exist in front of the update statement. Not in the middle as you have them. – xQbert
@xQbert I just tried your suggestion, unfortunately it didn't work. I found this suggesting that my syntax might work. stackoverflow.com/questions/4830191/… – cfatt10

2 Answers

59
votes

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
11
votes

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 ;