[GENERAL] 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
Re: [GENERAL] Update using non-existent fields does not throw an error
On 16 March 2015 at 17:02, Rob Richardson rdrichard...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update using non-existent fields does not throw an error
Thanks very much. Now that you've explained it, it should have been obvious. RobR -Original Message- From: Albe Laurenz [mailto:laurenz.a...@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 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
