Inner join returning wrong columns! Alias problem!!!!

[ILoveAccess]

Hello,

SQL Server SP 3, Access 2k on Windows 2k sp2,
I have a query:

SELECT * FROM t1 INNER JOIN t2 ON
t1.subcategory = t2.subcategory

In both tables, column subcategory is a varchar(3).
Each table has a column named category.

When I do SELECT t2.*, I get the correct t2 category,
but when I do SELECT *, I get t1's category column twice,
instead of t1.category and t2.category, I get 2 t1's.

My problem is I'm trying to do an update:

UPDATE t1 SET t1.category = t2.category FROM t1 INNER JOIN
t2 ON t1.subcategory = t2.subcategory

But the updated t1.category has the original t2.category!!!

Is this a bug, or is there something else going on. I
won't jump to that conclusion, but this query is very
simple compared to the ones I write everyday for years...

Thanks for your help.

 

[Vadim Rapp]

I> When I do SELECT t2.*, I get the correct t2
I> category, but when I do SELECT *, I get t1's
I> category column twice, instead of t1.category and
I> t2.category, I get 2 t1's.

Yes. Actually, Access gives a warning: the specified record source contains
duplicate names for some output fields.


I> My problem is I'm trying to do an update:

UPDATE t1 SET t1.category
= t2.category FROM t1
INNER JOIN t2 ON t1.subcategory =
t2.subcategory

I think

SET t1.category = t2.category

is exactly "updated t1.category has the original t2.category!!!", is it not?


Vadim

 

[IReallyLoveAccess]

Yes. Actually, Access gives a warning: the specified

record source contains
duplicate names for some output fields.

I don't receive this warning. The duplicate column names
are renamed Column0 Column1 Column2.

If I rename the column in table two to category_b, then
the correct data from table 2 is shown. But this is very
ugly, and doesn't work w/ the update column below.

I> My problem is I'm trying to do an update:

UPDATE t1 SET t1.category
= t2.category FROM t1
INNER JOIN t2 ON t1.subcategory =
t2.subcategory

I think

SET t1.category = t2.category

is exactly "updated t1.category has the original
t2.category!!!", is it not?

Nope. In SQL, you can update one column of a table with
another column in a separate table by inner joining on the
a common column, then setting the original column equal to
the column in the second table. I've done this 5 times a
day for 10 years.

here's a diagram:
-------t1-------- ---------t2--------
|-colA--|--colB-| |-colA---|---colB-|
----------------- -------------------
| 1 | T1 | | 1 | T2 |
----------------- -------------------
| 2 | T1 | | 2 | T2 |
----------------- -------------------
| 3 | T1 | | 3 | T2 |
----------------- -------------------

if you do a join on colA between t1 and t2 tables,
selecting *, you should get:
----------join on colA--------------
|-colA--|--colB-|-colA---|---colB-|
------------------------------------
| 1 | T1 | 1 | T2 |
-----------------------------------
| 2 | T1 | 2 | T2 |
------------------------------------
| 3 | T1 | 3 | T2 |
------------------------------------
What I'm getting is:
----------join on colA--------------
|-colA-|-colB-|-column0-|-column1-|
------------------------------------
| 1 | T1 | 1 | T1 |
-----------------------------------
| 2 | T1 | 2 | T1 |
------------------------------------
| 3 | T1 | 3 | T1 |
------------------------------------
Notice the duplicate column in column1. ADP is detecting
duplicate names, but instead of T2 in column1, I'm getting
'T1', so the colB is being repeated.

I've tried replacing * with explicit table & column names,
such as: SELECT T1.colA, T1.colB, T2.colA, T2.colB,
but I get the same results.

If you are able to, try to create these tables w/ data.
It should work for you. I'm still looking for the answer
to this one...one of the tables was created in Access,
although it's a sql server database.

Help, anyone?

 

[Guest]

Sorry Vadim!
you are correct:

is exactly "updated t1.category has the original

t2.category!!!", is it not?

it would be cool if it did that, but this is my typo.

updated t1.category has the original t1.category, instead
of the expected t2.category.

 

[Vadim Rapp]

I> UPDATE t1 SET t1.category = t2.category FROM t1
I> INNER JOIN t2 ON t1.subcategory = t2.subcategory

a> updated t1.category has the original t1.category,
a> instead of the expected t2.category.


(I assume IloveAccess, IreallyloveAccess, and anonymous@ are the same
entity. maybe it might be a good idea to stick to one nick)

How do you run the query in Access? In the initial post, you mentioned "I
have a query" - are we atalking about ADP, or MDB?

Also, are you sure the update is performed on the rows you are looking at?
If your join in FROM results in empty set, the update will succeed, but
affect zero records, and maybe you then look at the original values thinking
they were updated?

Vadim

 

[Guest]

Yes, I am actually the same person. I really do not love
Access, however.

I connect to SQL Server through An Access 2k ADP.
I am aware of the empty set scenario, which occurs
during updates where the join or where clause have no
matches, and so nothing is updated.

I know this is not the case, because as I mentioned
before, if I rename the columns so that each table has
different column names, it works as expected.

This works:

UPDATE T1 SET T1.A=T2.C FROM T1 INNER JOIN T2 ON
T1.B = T2.D

but this doesn't:

UPDATE T1 SET T1.A = T2.A FROM T1 INNER JOIN T2 ON
T1.B = T2.B

When I change the query to a select, like so:

SELECT T1.A, T1.B, T2.A, T2.B FROM T1 INNER JOIN T2 ON
T1.B = T2.B

I can see that the T2.A is the same column as T1.A,
And I can verify that this should not be true if I open
T2.A separately, or I copy the column from T2.A and create
a new column called T2.C, like this:

UPDATE T2 SET C = A FROM T2

Then

SELECT T1.A, T1.B, T2.A, T2.B, T2.C FROM T1 INNER JOIN T2
ON
T1.B = T2.B

T2.A doesn't equal T2.C!!!

By the way, the query is a stored procedure.

I'm only using Access because I didn't have Enterprise
Manager installed on the development machine, and I was
tired of switching workstations.

 

[Vadim Rapp]

a> This works:

a> UPDATE T1 SET T1.A=T2.C FROM T1 INNER JOIN T2 ON
a> T1.B = T2.D

a> but this doesn't:

a> UPDATE T1 SET T1.A = T2.A FROM T1 INNER JOIN T2 ON
a> T1.B = T2.B

I tried this exact query, and it did work as expected, i.e. it updated T1.A
with T2.A.

===========================
begin transaction
CREATE TABLE [dbo].[t1] (
[id] [int] IDENTITY (1, 1) NOT NULL primary key,
[a] [varchar] (50) NULL ,
[varchar] (50) NULL ,
)

INSERT INTO [DB].[dbo].[t1]([a], )
VALUES('Z','Y')

CREATE TABLE [dbo].[t2] (
[id] [int] IDENTITY (1, 1) NOT NULL primary key,
[a] [varchar] (50) NULL ,
[varchar] (50) NULL ,
)

INSERT INTO [DB].[dbo].[t2]([a], )
VALUES('A','Y')


select * from t1
UPDATE T1 SET T1.A = T2.A FROM T1 INNER JOIN T2 ON
T1.B = T2.B
select * from t1
rollback transaction
===========================
result:

id a b
----------- -------------------------------------------------- -------------
-------------------------------------
1 Z Y

id a b
----------- -------------------------------------------------- -------------
-------------------------------------
1 A Y







a> By the way, the
a> query is a stored procedure.

Which means that Access is not relevant at all, it only launches the stored
procedure, everything else is performed by SQL server. The only thing to
check that comes to my mind is the value in access
tools/options/advanced/default max records - set it to zero. But, to
eliminate all other possibilities, you can try running this query from
another database tool - for example, from excel/microsoft query, or from
some freeware database product, or programmatically from VBA code using pure
ADO. At least, then you'll know if this is on Access part or not.

Theoretically, it's possible that it's sql server error; however I hardly
imagine such a rude error existing even in early alpha of a database
product. Still, you can try updating your sql server to the latest release.


Vadim

 

[Guest]

If I set down at the Enterprise Manager or Query Analyzer,
the query executes as expected. I dropped both tables and
rebuilt them through EM, rebuilt the stored procedure as
well and all is fine. Don't know if it's relevant, but I
create one table through ADP, the other directly on SQL
Server.

Thanks for your helpfulness.