PostgreSQL simple UPDATE error

Question

I have a simple postgresql table defined as follows:

CREATE TABLE t_s (
    a integer,
    b integer
);

If I do a simple update statement UPDATE t_s SET a=1 WHERE b=1 I get an error:

Chyba SQL:

ERROR:  syntax error at or near "SET"
LINE 1: SELECT COUNT(*) AS total FROM (UPDATE t_s SET a=1 WHERE b=1)...
                                                  ^

Ve výrazu:
SELECT COUNT(*) AS total FROM (UPDATE t_s SET a=1 WHERE b=1) AS sub

Is there anything that can go wrong with this statement? I can't see any reason for this error. I use PostgreSQL 9.1.6.

Your error message shows that you are NOT running the UPDATE you claim to be running. — user330315
– user330315, Commented Jan 24, 2013 at 11:28

Frank Heikens · Accepted Answer · 2013-01-24 11:32:56Z

1

You need a common table expression for this:

WITH cte AS (
    UPDATE t_s SET a=1 WHERE b=1 RETURNING a
)
SELECT COUNT(*) AS total FROM cte;

Data modifying statements are available as of version 9.1

answered Jan 24, 2013 at 11:32

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL simple UPDATE error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related