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