Thanks very much. Now that you've explained it, it should have been obvious. RobR -----Original Message----- From: Albe Laurenz [mailto:laurenz.albe@xxxxxxxxxx] Sent: Monday, March 16, 2015 12:21 PM To: Rob Richardson; pgsql-general@xxxxxxxxxxxxxx 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 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
