Using a table name as its own alias in an update fails
We are using Informix XPS, the new version 8.30.
If we have an update statement, we cannot use an alias that
is the same as the name of the table itself.
This did work in the previous release.
What does ANSI SQL (any of them) say about this?
Note, alias is technically called a "correlation name".
How do other DBMS's handle this?
The book "SQL-99 Complete, Really" does not explictly address this
point, which I interpret to mean it is allowed.
Here are a few example statement to illustrate.
This fails with the error:
522: Table (test1) not selected in query
update profmart_prod:test1
set test1.first = test2.first
from profmart_prod:test1 test1 , test2
where test1.third = test2.third;
This fails with the same error:
update test1
set test1.first = test2.first
from profmart_prod:test1 test1 , test2
where test1.third = test2.third;
If the alias is on the second table it fails with a different and
bogus error message. This fails with the error message:
6036: Cannot open file 'all.iem'.
update test1
set test1.first = test2.first
from test1 , test2 test2
where test1.third = test2.third;
On the other hand all of the following work as expected:
update a
set a.first = test2.first
from profmart_prod:test1 a , test2
where a.third = test2.third;
update a
set a.first = b.first
from test1 a , test2 b
where a.third = b.third;
update a
set a.first = b.first
from profmart_prod:test1 a , profmart_prod:test2 b
where a.third = b.third;
update test1
set test1.first = b.first
from test1 , test2 b
where test1.third = b.third;
Thanks,
Steve
---
Steven Tolkin steve.tolkin@fmr.com 617-563-0516
Fidelity Investments 82 Devonshire St. R24D Boston MA 02109
There is nothing so practical as a good theory. Comments are by me,
not Fidelity Investments, its subsidiaries or affiliates.
The format you gave below is the most legal one.
update a
set a.first = b.first
from profmart_prod:test1 a , profmart_prod:test2 b
where a.third = b.third;
Steven Tolkin <Steve.Tolkin@fmr.com> wrote:
>We are using Informix XPS, the new version 8.30.
>If we have an update statement, we cannot use an alias that
>is the same as the name of the table itself.
>
>This did work in the previous release.
>
>What does ANSI SQL (any of them) say about this?
>Note, alias is technically called a "correlation name".
>
>How do other DBMS's handle this?
>
>The book "SQL-99 Complete, Really" does not explictly address this
>point, which I interpret to mean it is allowed.
>
>Here are a few example statement to illustrate.
>
>This fails with the error:
>522: Table (test1) not selected in query>
>update profmart_prod:test1
>set test1.first = test2.first
>from profmart_prod:test1 test1 , test2
>where test1.third = test2.third;
>
>This fails with the same error:
>update test1
>set test1.first = test2.first
>from profmart_prod:test1 test1 , test2
>where test1.third = test2.third;
>
>
>If the alias is on the second table it fails with a different and
>bogus error message. This fails with the error message:
>6036: Cannot open file 'all.iem'.>
>update test1
>set test1.first = test2.first
>from test1 , test2 test2
>where test1.third = test2.third;
>
>
>On the other hand all of the following work as expected:
>update a
>set a.first = test2.first
>from profmart_prod:test1 a , test2
>where a.third = test2.third;
>
>update a
>set a.first = b.first
>from test1 a , test2 b
>where a.third = b.third;
>
>update a
>set a.first = b.first
>from profmart_prod:test1 a , profmart_prod:test2 b
>where a.third = b.third;
>
>update test1
>set test1.first = b.first
>from test1 , test2 b
>where test1.third = b.third;
>
>
>Thanks,
>Steve
>---
>Steven Tolkin steve.tolkin@fmr.com 617-563-0516
>Fidelity Investments 82 Devonshire St. R24D Boston MA 02109
>There is nothing so practical as a good theory. Comments are by me,
>not Fidelity Investments, its subsidiaries or affiliates.
Steven Tolkin wrote:
>
> We are using Informix XPS, the new version 8.30.
> If we have an update statement, we cannot use an alias that
> is the same as the name of the table itself.
>
> This did work in the previous release.
>
> What does ANSI SQL (any of them) say about this?
> Note, alias is technically called a "correlation name".
>
> How do other DBMS's handle this?
>
> The book "SQL-99 Complete, Really" does not explictly address this
> point, which I interpret to mean it is allowed.
>
> Here are a few example statement to illustrate.
>
> This fails with the error:
> 522: Table (test1) not selected in query>
> update profmart_prod:test1
> set test1.first = test2.first
> from profmart_prod:test1 test1 , test2
> where test1.third = test2.third;
All of these queries look illegal to me, maybe it's a typo? Do you mean
something like this:
UPDATE profmart_prod:test1
SET test1.first = (
SELECT test2.first
FROM profmart_prod:test1 test1, test2
WHERE test1.third = test2.third
);
This is still not correct and I see why the error since the outer query,
ie the implied FETCH of the UPDATE, references a table known as test1
while the inner query creates an alias test1 which conflicts with the
test1 in the outer query. The optimizer (or parser?) does not know if the
references to 'test1.' in the WHERE clause of the inner query refer to the
alias test1 in the inner query or if this is a correllated sub-query and
it refers to the test1 in the outer query (the UPDATE). If previous XPS
versions did not complain then you were relying on the behavior of a
parser bug. The current behavior is correct. The problem is not that the
alias is the same as the table but that it conflicts with the containing
outer query's table name. Besides what's the big deal? Why not just use
a different alias?
> This fails with the same error:
> update test1
> set test1.first = test2.first
> from profmart_prod:test1 test1 , test2
> where test1.third = test2.third;
>
> If the alias is on the second table it fails with a different and
> bogus error message. This fails with the error message:
> 6036: Cannot open file 'all.iem'.
This one indicates a missing message file in the $INFORMIXDIR/msg
directory tree. Hmmmm. Looks like this coincidence is helping to cloud
the original issue. I don't know that this one has much to do with the
alias itself either.
> update test1
> set test1.first = test2.first
> from test1 , test2 test2
> where test1.third = test2.third;
>
> On the other hand all of the following work as expected:
> update a
> set a.first = test2.first
> from profmart_prod:test1 a , test2
> where a.third = test2.third;
>
> update a
> set a.first = b.first
> from test1 a , test2 b
> where a.third = b.third;
>
> update a
> set a.first = b.first
> from profmart_prod:test1 a , profmart_prod:test2 b
> where a.third = b.third;
>
> update test1
> set test1.first = b.first
> from test1 , test2 b
> where test1.third = b.third;
Art S. Kagel