Re: UPDATE server_table INNER JOIN local_table
From: Ron Hinds (__NoSpam_at___NoSpamramac.com)
Date: 04/13/04
- Next message: Jasper Smith: "Re: SQL Server 2000 Client Tool License"
- Previous message: Eric Huang: "SQL Server 2000 Client Tool License"
- In reply to: Sue Hoegemeier: "Re: UPDATE server_table INNER JOIN local_table"
- Next in thread: Sue Hoegemeier: "Re: UPDATE server_table INNER JOIN local_table"
- Reply: Sue Hoegemeier: "Re: UPDATE server_table INNER JOIN local_table"
- Messages sorted by: [ date ] [ thread ]
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.
>
- Next message: Jasper Smith: "Re: SQL Server 2000 Client Tool License"
- Previous message: Eric Huang: "SQL Server 2000 Client Tool License"
- In reply to: Sue Hoegemeier: "Re: UPDATE server_table INNER JOIN local_table"
- Next in thread: Sue Hoegemeier: "Re: UPDATE server_table INNER JOIN local_table"
- Reply: Sue Hoegemeier: "Re: UPDATE server_table INNER JOIN local_table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
