How to apply a SQL update for a list of values?

Question

I have a database with following tables an relationships

Requests
Responses
Payments

A payment can have multiple requests (one-to-many).
A request has one response (one-to-one).
A payment also has a field called SuccessfulResponseId which is the ID of a request which succeeds. (To be explicit, the response table does not contain a direct reference to the payments table, only an indirect one via the requests table)

I have an update SQL query which looks something like this:

update "Responses"
set "Status"              = correct_response."Status"
from (
       select "Status",
       from "Responses" 
       where "Responses"."Id" in (select "PaymentResponseId"
                          from "Requests"
                          where "Status" = 4
                            and "PaymentId" = '123456')
     ) correct_response

where "Id" in (Select "SuccessfulResponseId"
               from "Payments"
               where "Payments"."Id" = '123456')

In this case, the table schema contains two tables Requests and Responses. One request can have multiple responses.

The purpose of the update query above is to copy some data from one row in the response table to another row in the response table (where all responses belong to the same payment --- indirectly via requests tables)

Now, I have a bunch of Payments for which I want to run the query. Is there a SQL way to run this query for different Payment Ids. something like

-- I understand the following is not a valid syntax;
-- using it to just make the requirement more visual
(
update "Responses"
set "Status"              = correct_response."Status"
from (
       select "Status",
       from "Responses" 
       where "Responses"."Id" in (select "PaymentResponseId"
                          from "Requests"
                          where "Status" = 4
                            and "PaymentId" = X)
     ) correct_response

where "Id" in (Select "SuccessfulResponseId"
               from "Payments"
               where "Payments"."Id" = X)
) Where X in (select Id from Payments where ...some condition...)

Remember to disclose your version of Postgres. And actual `CREATE TABLE` statements are superior to any amount of verbal description for table definitions. — Erwin Brandstetter, Jul 23 '19 at 02:40

Erwin Brandstetter · Accepted Answer · 2019-07-23T03:00:46.850

The query you display only makes sense while the derived table correct_response returns a single row. Else, the result would be arbitrary - basically wrong. I see no such guarantee in your question. A partial unique index would do that (among other options):

CREATE UNIQUE INDEX ON "Requests" ("PaymentId")
WHERE  "Status" = 4;

And you need another unique constraint (or index) to make this unambiguous:

CREATE UNIQUE INDEX ON "Payments" ("SuccessfulResponseId");

Then (making some assumptions based on your description) your first query burns down to:

UPDATE "Responses" rs
SET    "Status" = rs0."Status"
FROM   "Payments"  py
JOIN   "Requests"  rq  ON rq."PaymentId" = py."Id"
JOIN   "Responses" rs0 ON rs0."Id" = rq."PaymentResponseId"
WHERE  py."Id" = '123456'
AND    rq."Status" = 4
AND    rs."Id" = py."SuccessfulResponseId"
AND    rs."Status" IS DISTINCT FROM rs0."Status";

And your task becomes simple: instead of identifying a single payment by "Id", filter a whole set with "some conditions":

UPDATE "Responses" rs
SET    "Status" = rs0."Status"
FROM   "Payments"  py
JOIN   "Requests"  rq  ON rq."PaymentId" = py."Id"
JOIN   "Responses" rs0 ON rs0."Id" = rq."PaymentResponseId"
WHERE  py.? = ? -- put some coditions on py here !!!
AND    rq."Status" = 4
AND    rs."Id" = py."SuccessfulResponseId"
AND    rs."Status" IS DISTINCT FROM rs0."Status";

The added AND rs."Status" IS DISTINCT FROM rs0."Status" avoids empty updates. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

But I am not convinced this kind of update should be necessary at all ...

Aside: legal, lower-case identifiers without double quotes make your life with Postgres simpler:

Are PostgreSQL column names case-sensitive?

How to apply a SQL update for a list of values?

1 Answers1