Help convert Access Query to SQL Script

  • Thread starter Thread starter Jeff via AccessMonster.com
  • Start date Start date
J

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?
 
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
 
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.
 
Back
Top