CODEWITHSUNDEEP
sqlpostgresql
user2024300
user2024300

Reputation: 169

Update with Subquery in postgresql

I try update postgresql table using subquery

        UPDATE
            bc
        SET
            (r, w) = ($1, $2)
        WHERE
            bc.sr_id IN (
                UPDATE
                    sr
                SET
                    (r, w) = ($1, $2)
                WHERE
                    si = $3 AND
                    rti = $4 AND
                    fc = $5
                RETURNING sr.id
            )

Why it's returns error?

Upvotes: 1

Views: 195

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

You can't chain DML statements like that. You will have to use a writable CTE.

WITH buz AS
(UPDATE foo
SET num=0 WHERE num>5 RETURNING num)
UPDATE bar SET num=0 WHERE num IN
(SELECT num FROM foo);
SELECT * FROM foo;
SELECT * FROM bar;

http://sqlfiddle.com/#!1/513a2/1

Upvotes: 2

Related Questions