CODEWITHSUNDEEP
sqlpostgresql
Ankit
Ankit

Reputation: 6980

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

I have a database with following tables an relationships

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...)

Upvotes: 0

Views: 424

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657892

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:

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:

Upvotes: 1

Related Questions