Thread: Update using non-existent fields does not throw an error
Greetings!
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?
RobR
On 16 March 2015 at 17:02, Rob Richardson <RDRichardson@rad-con.com> wrote: > Greetings! > > > > 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? Because inventory contains those fields. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
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
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