I am new to MSSQL and have some UPDATE queries from Access that use INNER JOIN that I'd like to use in the MSSQL version of the database. Copying the Access version into Analyzer and checking syntax just doesn't like the INNER JOIN line. Anyone got any ideas. Both tables have IDENTITY fields.

Thanks!

Dan

Hello,

In SQL, an inner join should work, here's an example:

select a.ID, a.Name from TableA a
inner join TableB b
on a.ID = b.AID

a and b are table aliases, and could be written out with the Table Name instead. This example uses the ID in table A (primary key) to relate to the foreign key (AID) in table b.

Brian

Hi Dan,

Can you post your query so that let us suggest you if any corretions needed?

Cheers!

_________________________
-Vijay G
Strive for Perfection

an update with inner join looks like this:

update x
set field1 = value1,
    field2 = value2,
    ...
from table1 x
    inner join table2 y on x.somefield = y.somefield

I usually find that solving UPDATEs that apparently require INNER JOIN need to use a subselect something like this...

UPDATE table1
set field1 = value1,
    field2 = value2,
    ...
WHERE Identity col IN(
SELECT Identity col....
from table1 x
    inner join table2 y on x.somefield = y.somefield
)