Thread: UPDATE statement with syntax error doesn't raise a warning?

I've ran an update statement like this (obviously wrong, I know!):

update my_table
  set boolean_field = true AND
  my_notes = 'something'
where id in
   (select id from my_table order by random() limit 4000);

in my psql client and I had a "UPDATE 4000" result but, correctly, anything was changed in my_table.

So... why pg didn't raise a warning about syntax issue?

Thank you!

Mirko

P.S.
PostgreSQL 8.3.8 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2
psql 8.3.8 (Ubuntu package)

Mirko Pace wrote:
> I've ran an update statement like this (obviously wrong, I know!):
>
> update my_table
>   set boolean_field = true AND
>   my_notes = 'something'
> where id in
>    (select id from my_table order by random() limit 4000);
>
> in my psql client and I had a "UPDATE 4000" result but, correctly,
> anything was changed in my_table.
>
> So... why pg didn't raise a warning about syntax issue?
>
> Thank you!
>
> Mirko
>
> P.S.
> PostgreSQL 8.3.8 on i686-pc-linux-gnu, compiled by GCC gcc (Debian
> 4.3.2-1.1) 4.3.2
> psql 8.3.8 (Ubuntu package)

are you sure its wrong?

maybe pg looked at it like:
boolean_field = (true AND my_notes = 'something')


-Andy

Mirko Pace <mirkop82@gmail.com> wrote:

> I've ran an update statement like this (obviously wrong, I know!):

> update my_table
>   set boolean_field = true AND
>   my_notes = 'something'
> where id in
>    (select id from my_table order by random() limit 4000);

> in my psql client and I had a "UPDATE 4000" result but, correctly, anything
> was changed in my_table.

> So... why pg didn't raise a warning about syntax issue?

Because there is no syntax error? "TRUE AND my_notes =
'something'" is a valid expression (and equivalent to
"my_notes = 'something'").

Tim

On Fri, 25 Sep 2009 17:05:13 +0200, Mirko Pace wrote about [GENERAL]
UPDATE statement with syntax error doesn't raise a warning?:

>I've ran an update statement like this (obviously wrong, I know!):

Not so obvious.

>update my_table
>  set boolean_field = true AND
>  my_notes = 'something'
>where id in
>   (select id from my_table order by random() limit 4000);
>
>in my psql client and I had a "UPDATE 4000" result but, correctly,
>anything was changed in my_table.

I presume you meant "nothing" rather than "anything".

>So... why pg didn't raise a warning about syntax issue?

If we add some redundant parentheses, the SET clause becomes:

SET boolean_field = (true AND my_notes = 'something')

As you can see, this is a valid Boolean expression. There is no syntax
error. It just doesn't mean what you wanted.
--
Regards,

Dave  [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================