CODEWITHSUNDEEP
sqlpostgresqlsql-updatecase
Joe M
Joe M

Reputation: 3420

How to use a CASE statement after FROM before SELECT in UPDATE query

I'm not sure if this is possible, but the essence of what I'm trying to do is have two separate SELECTS, one for a single result, one for the union of two results. Note: the text in {brackets} is just a generic substitution. The below gives me a syntax error at CASE:

UPDATE MySchema.MyTable as MyTable
SET {column_names} = {new values}
FROM
CASE
    WHEN {case A} THEN
        (SELECT {column_names}
        FROM MySchema.MyOtherTable
        WHERE {conditions}
        LIMIT 1)
    ELSE
        ((SELECT {column_names}
        FROM MySchema.MyOtherTable
        WHERE {conditions}
        LIMIT 1)
        UNION
        (SELECT {column_names}
        FROM MySchema.MyOtherTable
        WHERE {other_conditions}
        LIMIT 1))
END CASE;
AS MyOtherTable
WHERE MyTable.product_id = MyOtherTable.product_id

I've had this query working with the UPDATE-SET-FROM portion duplicated inside each of the two cases, but I'm trying to trim out redundancies in the query if possible. I also tried moving the FROM inside each case, but that gave a syntax error too.

Upvotes: 1

Views: 761

Answers (1)

Mureinik
Mureinik

Reputation: 311478

You can't use a case expression to put together the objects used in a statement. You could, however, use logical operators to emulate this construct, so the values with {other_conditions} are only used if {case A} does not occur:

UPDATE MySchema.MyTable as MyTable
SET {column_names} = {new values}
FROM
((SELECT {column_names}
FROM MySchema.MyOtherTable
WHERE {conditions}
LIMIT 1)
UNION
(SELECT {column_names}
FROM MySchema.MyOtherTable
WHERE (NOT ({case A})) AND ({other_conditions}) -- Here!
LIMIT 1))

Upvotes: 1

Related Questions