From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Rob Richardson *EXTERN*" <RDRichardson(at)rad-con(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update using non-existent fields does not throw an error |
Date: | 2015-03-16 16:20:40 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B365A77BB@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Richardson | 2015-03-16 16:28:32 | Re: Update using non-existent fields does not throw an error |
Previous Message | Alban Hertroys | 2015-03-16 16:07:21 | Re: Update using non-existent fields does not throw an error |