How I can update one to many relation in Postgres?

Question

Here is my first table question.

CREATE TABLE "question" (
"question_id" serial NOT NULL,
"question" TEXT NOT NULL UNIQUE,
"added_at" TIMESTAMP NOT NULL,
"question_marks" integer NOT NULL,
CONSTRAINT "question_pk" PRIMARY KEY ("question_id"))

Questions have many options, so I referring every option row with question_id

CREATE TABLE "option" (
"option_id" serial NOT NULL,
"option" TEXT NOT NULL,
"option_question_id" integer NOT NULL,
"option_correct" BOOLEAN NOT NULL,
CONSTRAINT "option_pk" PRIMARY KEY ("option_id"))

ALTER TABLE "option" ADD CONSTRAINT "option_fk1" FOREIGN KEY ("option_question_id") REFERENCES "question"("question_id") ON DELETE CASCADE;

Now, How can I update both tables in one query?

I building an API. The below-given output is for request. The request will response with question details and options for the question.

I am able to update question but questions have many options, How can I update options?

    "questionDetails": [
        {
            "question_id": 30,
            "question": "What is gravity of Saturn?",
            "added_at": "2020-02-20T18:30:00.000Z",
            "question_marks": 1
        }
    ],
    "options": [
        {
            "option_id": 19,
            "option": "20",
            "option_question_id": 30,
            "option_correct": true
        },
        {
            "option_id": 20,
            "option": "30",
            "option_question_id": 30,
            "option_correct": false
        },
        {
            "option_id": 21,
            "option": "40",
            "option_question_id": 30,
            "option_correct": false
        },
        {
            "option_id": 18,
            "option": "400000000",
            "option_question_id": 30,
            "option_correct": false
        }
    ]
}

Now Can I update this relation?

You need to explain how you want to update the tables. Sample data and desired results would help. — Gordon Linoff, Feb 21 '20 at 11:50

score 2 · Accepted Answer · answered Feb 21 '20 at 12:59

You can chain multiple operations together in a single query by using CTEs that have returning clauses.

with
    __parent as(
        update
            my_schema.parent_table
        set
            col_1 = 'a',
            col_2 = 'b'
        where
            id_col = 3
        returning
            id_col
    )
update
    my_schema.child_table
set
    col_1 = 'c'
where
    parent_id = (select id_col from __parent)

The same thing can be done for insert and delete statements.

Do note you actually need to select from the CTE in the following query, otherwise the statement within will not be executed.

How I can update one to many relation in Postgres?

1 Answers1

Linked