Sql – UPDATE with WITH and CASE – PostgreSQL

postgresqlsql

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

Best Answer

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

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.