PostgreSQL - Synax error in Update Query

Question

Please check the query below.

update product set product_price = 5 where product_price = 0
ERROR:  syntax error at or near "set" at character 45

SQL error:

ERROR:  syntax error at or near "set" at character 45

In statement:

SELECT COUNT(*) AS total FROM (update product set product_price = 5 where product_price = 0) AS sub

I don't know why I am getting this error. Please help me.

Answer 1

with s as (
    update product
    set product_price = 5
    where product_price = 0
    returning product_price
)
select count(*)
from s

Answer 2

update statement does not return values which can be used in select.

If you want to know how many rows were affected you, according to this can use

GET DIAGNOSTICS my_variable = ROWCOUNT;

There are ways to do it programatically, but how to do it depends on the language used.

Answer 3

The SELECT COUNT(*) AS total FROM ( yourquery ) wrapping seems to be caused by having the checkbox "paginate results" checked. If you uncheck that box, your update should work.