Update on inner join possible?

[Jay Dougherty]

I'm new to T-SQL, and could use some advice on how to accomplish the
following:
I'll strip it down to a simple example. Table A has 2 columns: FileNo and
Data. Table B also has 2 columns: FileNo and Data. What I'm trying to do
is collapse the Data field in Table B into Table A.

Table A's data (FileNo, Data) is
1, 'Test'

Table B's data (FileNo, Data) is
1, ',First Test'
1, ',Second Test'
1, ',Third Test'

I want to end up with Table A's Data field containing 'Test,First
Test,Second Test,Third Test'

In Access SQL, the following would do it:

UPDATE [Table A] INNER JOIN [Table B] ON [Table A].FileNo = [Table B].FileNo
SET [Table A].Data = [Table A].[Data] & [Table B].[Data];

In T-SQL, I can (apparently) only use a single table in the Update clause.
When I tried the following:

update [Table A]
Set [Table A].Data = a.Data + b.Data
from [Table A] a inner join [Table B] b
on a.FileNo = b.FileNo

I end up with [Table A].data containing 'Test,First Test'. This is not too
surprizing since Table A has only one row, but is there a way to accomplish
what I want in T-SQL?

Thanks for any advice.

Jay

[vze2chqg]

I think what you need is a SP with 2 cursors in it, one nested in the other.
The first cursor will allow you to loop through table A's records one at a
time and set a 'FileNumVar' variable to the current record's FileNum. Then,
the second cursor will loop through the second table, looking for records
matching the FileNum variable, adding (concatenating) the current value of
Data to a variable like 'DataString'. Finally, one the entire recordset has
been looped through by the second cursor, Update the 1st table's Data field
with the 'DataString' variable where FileNum=FileNumVar, then loop to the
next FileNum.

It may not be the only way to do it, but I think it should work. This will
also take into account if Table B has varying number of records for each
FileNum.

Lee Mychajluk
Wertheim Inc.

"Jay Dougherty" <jaydou...@worldnet.att.net> wrote in message
news:Fkzp7.24633$Uf1.2...@bgtnsc06-news.ops.worldnet.att.net...

[--CELKO--]

>> I'll strip it down to a simple example. Table A has 2 columns:
FileNo and
Data. Table B also has 2 columns: FileNo and Data. What I'm trying
to do
is collapse the Data field in Table B into Table A. <<

If you'd post DDL, we would know what the keys are, instead guessing
at it.

CREATE TABLE A
(file_nbr INTEGER NOT NULL PRIMARY KEY,
data CHAR(10) NOT NULL);

CREATE TABLE B
(file_nbr INTEGER NOT NULL,
data CHAR(10) NOT NULL,
PRIMARY KEY (file_nbr, data));

>> I want to end up with Table A's Data field containing 'Test,First
Test,Second Test,Third Test' <<

Don't do this -- get a book and read about First Normal Form.

>> In Access SQL, the following would do it:

UPDATE [Table A] INNER JOIN [Table B] ON [Table A].FileNo = [Table
B].FileNo
SET [Table A].Data = [Table A].[Data] & [Table B].[Data]; <<

In SQL, this means that you are updating the result of the INNER JOIN;
that working table disappears at the end of the statement. Kinda
useless, unh?

ACCESS is not SQL -- not even close! It will take about a year to
un-learn it.

If you reallllly, realllly want to scew up your database, we can tell
you how to use a scalar subquery and cross joins to do this. But it
is like asking what is the best kind of rock to use to drive screws
into fine furniture.