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.mob ileno =
Cancellations.m obileno set ClientTable .ExpiryReason = 888,
Cancellations.C ancelled = 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]

Re: Simple join problem (I think!)



RS200Phil wrote:[color=blue]
> 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.mob ileno =
> Cancellations.m obileno set ClientTable .ExpiryReason = 888,
> Cancellations.C ancelled = 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[/color]

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

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

--
Bri

[Anthony England]

Re: Simple join problem (I think!)

"RS200Phil" <philsowden@dat aservicesltd.co .uk> wrote in message
news:1138644205 .614132.301400@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> 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.mob ileno =
> Cancellations.m obileno set ClientTable .ExpiryReason = 888,
> Cancellations.C ancelled = 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[/color]


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.