Simple join problem (I think!)

[RS200Phil]

Hi,

I'm tearing my hair out on the simplest of queries (SQL Server 2k).
I'm using ASP and the code I was using worked fine when attached to an
Access MDB, but I can't crack it under SQL Server.

This is the code I'm trying in the SQL Query analyzer:

Update ClientTable inner join Cancellations on ClientTable.mobileno =
Cancellations.mobileno set ClientTable .ExpiryReason = 888,
Cancellations.Cancelled = true

The word "join" is greyed out and I'm getting the error:
Incorrect syntax near the keyword 'inner'.

I'm sure it's something obvious, but I can't see the wood for the
trees!

Can anyone help???

TIA

Phil

[Bri]

RS200Phil wrote:

Hi,

I'm tearing my hair out on the simplest of queries (SQL Server 2k).
I'm using ASP and the code I was using worked fine when attached to an
Access MDB, but I can't crack it under SQL Server.

This is the code I'm trying in the SQL Query analyzer:

Update ClientTable inner join Cancellations on ClientTable.mobileno =
Cancellations.mobileno set ClientTable .ExpiryReason = 888,
Cancellations.Cancelled = true

The word "join" is greyed out and I'm getting the error:
Incorrect syntax near the keyword 'inner'.

I'm sure it's something obvious, but I can't see the wood for the
trees!

Can anyone help???

TIA

Phil

There is a space where there shouldn't be one;
"set ClientTable .ExpiryReason" should be "set ClientTable.ExpiryReason"

Not sure why this would make it think the Join is bad?

--
Bri

[Anthony England]

"RS200Phil" <ph********@dataservicesltd.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi,

I'm tearing my hair out on the simplest of queries (SQL Server 2k).
I'm using ASP and the code I was using worked fine when attached to an
Access MDB, but I can't crack it under SQL Server.

This is the code I'm trying in the SQL Query analyzer:

Update ClientTable inner join Cancellations on ClientTable.mobileno =
Cancellations.mobileno set ClientTable .ExpiryReason = 888,
Cancellations.Cancelled = true

The word "join" is greyed out and I'm getting the error:
Incorrect syntax near the keyword 'inner'.

I'm sure it's something obvious, but I can't see the wood for the
trees!

Can anyone help???

TIA

Phil

You are writing ASP with a connection to a SQL Server database? It doesn't
really have much to do with MS Access, does it? Anyway, apart from the
extra space which has been pointed out, there is a more fundamental issue.
SQL statements for Access (Jet) and those for SQL Server are not necessarily
interchangeable - what might work in one might not in the other. Update
queries are one of those things. You cannot hope to update two tables
simultaneously with one SQL statement. Either create a stored procedure to
do this or wrap two updates in a single transaction.