Thanks very much. Now that you've explained it, it should have been obvious.
RobR
-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Monday, March 16, 2015 12:21 PM
To: Rob Richardson; pgsql-general@postgresql.org
Subject: RE: Update using non-existent fields does not throw an error
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
viewthat 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
