Simple join problem (I think!)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RS200Phil - 30 Jan 2006 18:03 GMT

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

Reply to this Message

Bri - 30 Jan 2006 18:53 GMT

> Hi,
>
[quoted text clipped - 19 lines]
>
> 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

Reply to this Message

Anthony England - 30 Jan 2006 20:22 GMT

> Hi,
>
[quoted text clipped - 19 lines]
>
> 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.

Reply to this Message