Help convert Access Query to SQL Script

[Jeff via AccessMonster.com]

I have the following query in Access that I'm trying to convert to a SQL
Script to be run on a SQL Server database. Here's the code in Access:

UPDATE individual LEFT JOIN location ON individual.OLDLOCID = OLDSYSID SET
individual.LOCID = location.SYSID
WHERE location.OLDSYSID Is Not Null

My problem is that the SQL in Access and SQL Server 2000 don't always
translate perfectly. Specifically, it seems you cannot use JOINS in an action
query. To get around this I'm thinking I have to do something like:

UPDATE individual
SET individual.LOCID = (SELECT SYSID FROM location WHERE OLDSYSID = ???)

But how do I get the SELECT statment to return just one record. Do I need a
temp. variable?

 

[mistux]

I have been migrating my Access queries to SQL2000 for about a year no
(one big upgrade project) and I encountered the same issue. Here i
what I did and do.

The joins go "away" and you have to put the joins in a WHERE clause.

So, I think it would look like this (but test it out first, my skill
are not great even after a year)

UPDATE individual
SET individual.LOCID = location.SYSID
WHERE location.OLDSYSID Is Not Null
AND individual.OLDLOCID = OLDSYSI

 

[Vadim Rapp]

Hello Jeff:
You wrote on Mon, 13 Jun 2005 14:05:17 GMT:

JvA> I have the following query in Access that I'm trying to convert to a
JvA> SQL Script to be run on a SQL Server database. Here's the code in
Access:

JvA> UPDATE individual LEFT JOIN location ON individual.OLDLOCID = OLDSYSID
JvA> SET individual.LOCID = location.SYSID
JvA> WHERE location.OLDSYSID Is Not Null


update individual SET individual.LOCID = location.SYSID
from individual LEFT OUTER JOIN location ON individual.OLDLOCID = OLDSYSID
WHERE location.OLDSYSID Is Not Null


Vadim Rapp

 

[Sylvain Lafontaine]

Quick note: maybe the first poster wanted to write « individual.OLDLOCID =
location.OLDSYSID » instead of « individual.OLDLOCID = OLDSYSID ».
Otherwise I'm not sure of understanding the exact effect of this LEFT JOIN.

Also, using a LEFT JOIN instead of an INNER JOIN might be superfluous when
right after there is a « WHERE location.OLDSYSID Is Not Null » but again,
I'm not really sure to understand what he is trying to do.

 

[Jeff via AccessMonster.com]

Thanks Vadim. Works like a charm.