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.

user2003356
  • 445
  • 2
  • 11
  • 24
  • Your statement does not make any sense. Why are you wrapping the `update` into a `select`. What are you really trying to do? –  Aug 29 '13 at 12:08
  • 1
    It's a `phppgadmin` problem. See http://stackoverflow.com/questions/18368831/postgres-sql-insert-query-syntax-error – Daniel Vérité Aug 29 '13 at 13:01
  • @DanielVérité is correct! Just unclick the checkbox "Paginate Results"... – RayOnAir Jun 12 '15 at 16:26

3 Answers3

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.

Dariusz
  • 21,561
  • 9
  • 74
  • 114
  • Thanks...I could not able to execute update query. I am getting an error. do you know why? – user2003356 Aug 29 '13 at 11:26
  • 1
    Because your query is invalid! You can not use update as subquery for a select. This is not possible in postgres. – Dariusz Aug 29 '13 at 11:28
  • 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. – user2003356 Aug 29 '13 at 11:30
  • 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. – Dariusz Aug 29 '13 at 11:33
  • 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? – user2003356 Aug 29 '13 at 11:36
  • I don't understand why are you getting angry. You are not being clear, I am trying to help you. Remember that. Your update syntax is fine. Maybe you're missing a schema name? or it's not product but product**s**? – Dariusz Aug 29 '13 at 11:39
  • @Dariusz that's right, phppgadmin does this. and this is valid – keltar Aug 29 '13 at 11:40
  • Really Sorry Dariusz!!..Thanks Keltar. – user2003356 Aug 29 '13 at 11:44
  • @user2003356 are you 100% sure table and column names are correct? Because i see nothing wrong with query – keltar Aug 29 '13 at 11:47
  • `GET` is `plpgsql` only. – Clodoaldo Neto Aug 29 '13 at 11:52
  • @ClodoaldoNeto the question is explicitely about pgsql, as tag suggests. – Dariusz Aug 29 '13 at 12:03
  • `plpgsql` is a procedural language used to build Postgresql functions. It is not sql. http://www.postgresql.org/docs/9.2/static/plpgsql.html – Clodoaldo Neto Aug 29 '13 at 12:08
  • @keltar, I am sure. It is right. – user2003356 Aug 29 '13 at 12:29
1
with s as (
    update product
    set product_price = 5
    where product_price = 0
    returning product_price
)
select count(*)
from s
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
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.

Erwin Anema
  • 1
  • 1