Update with Subquery in postgresql

Question

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?

What is the error message?

Taryn
– Taryn

2013-03-06 10:32:43 +00:00
Commented Mar 6, 2013 at 10:32 — Taryn
– Taryn, Commented Mar 6, 2013 at 10:32

Jakub Kania · Accepted Answer · 2013-03-06 10:45:13Z

2

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

edited Mar 6, 2013 at 10:45

answered Mar 6, 2013 at 10:36

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frank Heikens Over a year ago

Available as of version 9.1 and a reason to upgrade if you're using an older version.

Collectives™ on Stack Overflow

Update with Subquery in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related