Re: UPDATE server_table INNER JOIN local_table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ron Hinds (__NoSpam_at___NoSpamramac.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 14:05:34 -0700

Thanks folks for your help and here is the original question. Still looking
for a solution:

I've been converting a large Access 97 app to use SQL 2000 for the BE. I
have solved pretty much all of the performance scenarios except this one:
updating a server table and joining it to a local table, e.g.:

UPDATE server_table INNER JOIN local_table ON server_table.field1 =
local_table.field1 SET server_table.field2 = local_table.field2

The opposite scenario, i.e. update a local table and joining it to a server
table, I have solved by creating a View on the server with the relevant
fields constrained by some criteria. Unfortunately, for the other scenario I
don't usually know the constraint ahead of time, so creating a View would be
moot as it would contain all of the records in the original table. Any
suggestions?

I can't use a pure SQL server solution because one table is local to the
Access app - i.e., in a .MDB file (or can I?).

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:oigd70dd65o7iqduq5hg7ko4dkh2ctl72f@4ax.com...
> I don't have the original question but to update one table
> with values from another table, you can use something like:
>
> update tableA
> set TableAColumn = (select tableB.TableBColumn
> from tableB
> where TableB.SomeCol =
> TableA.SomeCol)
>
> -Sue
>
> On Thu, 08 Apr 2004 21:20:36 -0700, Tim Roberts
> <timr@probo.com> wrote:
>
> >This syntax is simply not going to solve his problem. The UPDATE query
> >can't find [tblSetUpARTemp].[arNewOpen], because tblSetUpARTemp is not
part
> >of the query. The subquery does limit the scope of the update, but it
does
> >not expose tblSetUpARTemp to the outside. That's why he gets the
"expected
> >1 parameter" thing; it's looking for the value of [arNewOpen].
> >
> >I'm not aware of any method in standard SQL to update records in one
table
> >using contents from corresponding records in another table.
>

Relevant Pages

  • Re: UPDATE server_table INNER JOIN local_table
    ... Thanks folks for your help and here is the original question. ... I've been converting a large Access 97 app to use SQL 2000 for the BE. ... i.e. update a local table and joining it to a server ... for the other scenario I ...
    (microsoft.public.sqlserver.clients)
  • Re: UPDATE server_table INNER JOIN local_table
    ... Thanks folks for your help and here is the original question. ... I've been converting a large Access 97 app to use SQL 2000 for the BE. ... i.e. update a local table and joining it to a server ... for the other scenario I ...
    (microsoft.public.sqlserver.odbc)
  • Re: New web server design with SQL back-end
    ... > Still in the design process of a Windows 2003 web server with a SQL ... > Scenario 1: ...
    (microsoft.public.windows.server.sbs)
  • Re: UPDATE server_table INNER JOIN local_table
    ... How you do it depends upon how you are accessing the SQL ... If you are using linked tables to the SQL Server data, ... for the other scenario I ...
    (microsoft.public.sqlserver.odbc)
  • Re: UPDATE server_table INNER JOIN local_table
    ... How you do it depends upon how you are accessing the SQL ... If you are using linked tables to the SQL Server data, ... for the other scenario I ...
    (microsoft.public.sqlserver.clients)