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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
