1

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.

Improve this question
3
  • Your statement does not make any sense. Why are you wrapping the update into a select. What are you really trying to do? Commented Aug 29, 2013 at 12:08
  • 1
    It's a phppgadmin problem. See stackoverflow.com/questions/18368831/… Commented Aug 29, 2013 at 13:01
  • @DanielVérité is correct! Just unclick the checkbox "Paginate Results"... Commented Jun 12, 2015 at 16:26

3 Answers 3

Reset to default
1

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.

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

13 Comments

Thanks...I could not able to execute update query. I am getting an error. do you know why?
Because your query is invalid! You can not use update as subquery for a select. This is not possible in postgres.
Please understand, my question is update product set product_price = 5 where product_price = 0 ERROR: syntax error at or near "set" at character 45. That's solve. I got the select query in phppgadmin response.
The problem is not the update, the problem is the select that is used around the update; remove the select, leave the update statement only and it will work properly.
OMG!!! I just pasted the above update query alone in phppgadmin. After that executed it. The Phppgadmin returned the "SQL error:" and "In statement:" lines in Error window. Is it clear?
|
1 
with s as (
    update product
    set product_price = 5
    where product_price = 0
    returning product_price
)
select count(*)
from s
Improve this answer

6 Comments

Thanks Neto, but I am getting "SQL error: ERROR: syntax error at or near "as" at character 39".
@user What version of Postgresql? select version();
It works ok. Check this fiddle sqlfiddle.com/#!12/b55e1/2
Version: PostgreSQL 8.1.23 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52)
@user with is a 8.4+ feature. Upgrade to 9.2 immediately.
|
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.