UPDATE server_table INNER JOIN local_table -- sql server clients

sql server clients : UPDATE server_table INNER JOIN local_table

Ron Hinds
4/7/2004 4:33:56 PM

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 = some_value

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?

Tenaya
4/7/2004 8:14:36 PM

Ron,

Try something closer to:

UPDATE server_table
SET field2 = some_value
WHERE EXISTS (SELECT *
FROM local_table
WHERE local_table.field1 =
server_table.field1)

This is ANSI standard coding. The Access syntax is not ANSI standard. SQL
Server, as well as most other enterprise level databases, support the ANSI
standard syntax.

Chief Tenaya

[quoted text, click to view]

Ron Hinds
4/8/2004 10:21:56 AM

Hi Chief,

Thanks for your suggestion. I tried that syntax but the actual problem is a
bit more complicated than I originally posted. Here is the original SQL
(probably generated by the Access Query Builder):

UPDATE tblSetUpARTemp INNER JOIN tblAR ON [tblSetUpARTemp].[arInvoiceNumber]
= [tblAR].[arInvoiceNumber] SET [tblAR].[arOpenAmount] =
[tblSetUpARTemp].[arNewOpen] WHERE
[tblSetUpARTemp].[arNewOpen]<>[tblAR].[arOpenAmount]

I modified it to conform to the syntax you suggested, see here:

UPDATE tblAR SET [tblAR].[arOpenAmount] = [tblSetUpARTemp].[arNewOpen] WHERE
EXISTS(SELECT * FROM tblSetUpARTemp WHERE tblSetUpARTemp.arInvoiceNumber =
TblAR.arInvoiceNumber) AND
[tblSetUpARTemp].[arNewOpen]<>[tblAR].[arOpenAmount]

But Access gives an error "Too few parameters, expected 1". When I paste the
SQL into a new Access Query and execute it it asks for the value of
[tblSetUpARTemp].[arNewOpen]. In re-reading your post, it appears that you
are suggesting that syntax because it is syntax that will work on SQL
Server. But the query (AFAIK) must be run using the Access Database Engine
since it is operating on a local (i.e. Access) table.

[quoted text, click to view]

Ron Hinds
4/8/2004 11:57:03 AM

Chief,

I'm using a native Access DB as the front-end. In my original post I said it
was an Access 97 app, which doesn't support any other format. I guess this
isn't exactly the right forum?

Tried changing the syntax with the same result. Still looking...

- Ron

[quoted text, click to view]

Tenaya
4/8/2004 1:50:04 PM

Ron,

Sorry that the syntax I supplied is not working, but I have to admit to a
bit of confusion. I'm not sure if you are using a native Access database
(.mdb) or the newer Access Data Pages. If the latter, then it is my
understanding that the database underlying Access Data Pages is SQL Server,
and the syntax I gave should work with all versions of SQL Server.

Perhaps you could supply a bit additional information. For example: from
where are you entering and executing the UPDATE command.

In the meantime, try the (admittedly slight) modification below, and see if
that works:

UPDATE tblAR
SET arOpenAmount = [tblSetUpARTemp].[arNewOpen]
WHERE EXISTS (SELECT * FROM tblSetUpARTemp
WHERE tblSetUpARTemp.arInvoiceNumber =
tblAR.arInvoiceNumber
AND
[tblSetUpARTemp].[arNewOpen]<>[tblAR].[arOpenAmount])

OK, having typed the above, I think, maybe, I know where the problem is. I
think you might have a misplaced closing parathesis in your syntax.

Chief Tenaya

[quoted text, click to view]

Tenaya
4/8/2004 4:28:43 PM

Ron,

If I understand what you have ... an Access 97 front-end to a SQL Server
database ... then it's my understanding that if you compose a query in
Access using the query builder mechanism, the ODBC driver between Access and
SQL Server will essentially change on the fly the syntax to conform to the
requirements of SQL Server. So I'm really not sure what's going wrong.

Sorry that I can't be of more help, but perhaps someone in the Access
newsgroups can be of more help.

Chief Tenaya

[quoted text, click to view]

Tim Roberts
4/8/2004 9:20:36 PM

[quoted text, click to view]

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.
--
- Tim Roberts, timr@probo.com

Sue Hoegemeier
4/9/2004 9:45:39 AM

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
[quoted text, click to view]

Tim Roberts
4/10/2004 8:51:12 PM

[quoted text, click to view]

Do you know if that ends up running N different subqueries? Or are the SQL
optimizers smart enough to do one subquery and align the values?
--
- Tim Roberts, timr@probo.com

Sue Hoegemeier
4/12/2004 10:50:16 AM

A correlated subquery is also known as a repeating subquery.
They are dependent on the outer query values and execute for
each row selected in the outer query. You can find an
explanation of this in books online under the topic:
Correlated Subqueries

-Sue

On Sat, 10 Apr 2004 20:51:12 -0700, Tim Roberts
[quoted text, click to view]

Ron Hinds
4/13/2004 2:05:34 PM

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?).

[quoted text, click to view]

Sue Hoegemeier
4/14/2004 8:01:23 AM

How you do it depends upon how you are accessing the SQL
Server data. If you are using just ADO (or you could be
using DAO I guess) then you would need to do this in code.
If you are using linked tables to the SQL Server data, you
can use pure SQL.
If you are using only PT queries, off the top of my head,
I'm not sure how you'd do this....maybe create a temp table
(i.e. don't add the table to the table defs collection) for
the PT results and then use SQL.
Access may have another way that this is supported. The
option I posted was the ANSI standard way. Different SQL
extensions sometimes support different ways to do this.
Being that you mention "local" tables, then this isn't an
ADP application so you'd still have Jet/Access extensions to
SQL available to use.

-Sue

On Tue, 13 Apr 2004 14:05:34 -0700, "Ron Hinds"
[quoted text, click to view]

Tim Roberts
4/14/2004 10:19:46 PM

[quoted text, click to view]

I believe Sue provided the final answer. You need to say this:

UPDATE server_table
SET field2 = (
SELECT local_table.field2
FROM local_table
WHERE local_table.field1 = server_table.field1
);

and it is going to take a long time.
--
- Tim Roberts, timr@probo.com

Ron Hinds
4/15/2004 9:37:13 AM

[quoted text, click to view]

Yes, that was the conclusion I came to - that it would take a long time. I
think I will solve it using two recordsets. Thanks everyone for your help!

Jacek Segit
4/18/2004 10:12:59 AM

Witaj Ron,
W Twoim li¶cie datowanym 15 kwietnia 2004 (18:37:13) mo¿na przeczytaæ:

[quoted text, click to view]

RH> Yes, that was the conclusion I came to - that it would take a long time. I
RH> think I will solve it using two recordsets. Thanks everyone for your help!

Sometime I use little different syntax:

UPDATE server_table SET field2 = local_table.field2 FROM local_table
WHERE local_table.field1 = server_table.field1

and if it would take a long time, maybe try to add index on that
fields (local.field1, server.field1)?

Regards
Jacek Segit

Tim Roberts
4/18/2004 8:24:52 PM

[quoted text, click to view]

That syntax is not in SQL92. Does SQL Server actually support the FROM
clause in an UPDATE statement?

If so, then this whole discussion has been kind of silly.
--
- Tim Roberts, timr@probo.com

Ron Hinds
4/19/2004 1:35:48 PM

[quoted text, click to view]

Yes, it does. I use it when I am joining to server tables, like so:

UPDATE server_table1 SET server_table1.field1 = server_table2.field1 FROM
server_table1 INNER JOIN server_table2 ON server_table1.field2 =
server_table2.field2

[quoted text, click to view]

How so? The original question was what was the best method of updating a
server table JOINed to a loca table.

[quoted text, click to view]

Sue Hoegemeier
4/19/2004 4:53:40 PM

Being that you are executing this query from Access, it
would need to be something that Access supports.
As someone pointed out, using FROM some table, etc is not
ansi sql. That's a t-sql specific extension to SQL. Using
the subquery is ansi sql. As I posted early on, Access may
have some extension of it's own but I don't know off hand.
But for an Access extension/query, you'd have to be using
linked tables with the SQL Server tables.

-Sue

On Mon, 19 Apr 2004 13:35:48 -0700, "Ron Hinds"
[quoted text, click to view]

Law
6/10/2004 9:46:18 PM

If the mdb will always be in the same place then you can do it all from the
SQL server side using OPENDATASOURCE or OPENROWSET. But that would likely be
just as slow.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

View Other Months
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn