Search Postgresql Archives

Re: Update using non-existent fields does not throw an error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Rob Richardson wrote:
> An update query is apparently succeeding, even though the query refers to fields that do not exist.
> Here’s the query:
> 
> update inventory set
> x_coordinate = (select x_coordinate from bases where base = '101'),
> y_coordinate = (select y_coordinate from bases where base = '101')
> where charge = 100
> 
> -- select x_coordinate, y_coordinate from bases where base = '101'
> 
> When I run the update query, it tells me that the query succeeded and that four records were updated,
> which is what I expect.  But when I looked at the inventory table, I found that the four records were
> unchanged.  So, I tried to check the values of the base coordinates by running the select statement
> shown above.  That statement threw an error complaining that x_coordinate and y_coordinate did not
> exist.  This is correct; I should have been querying a view that includes those fields.  But why
> didn’t the update statement throw an error?

That's an old one.

Since there is no "x_coordinate" in "bases", the column will refer to "x_coordinate" from
the outer query.  So you set "x_coordinate" and "y_coordinate" to their old values.

You can avoid problems like that by using column names that are qualified with the table name.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux