UPDATE server_table INNER JOIN local_table

[Ron Hinds]

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]

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

 

[Ron Hinds]

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.

 

[Tenaya]

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

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.

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


scenario would

 

[Ron Hinds]

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

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

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.

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


I've been converting a large Access 97 app to use SQL 2000 for the

BE.

I

 

[Tenaya]

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

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

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

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.

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


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?

 

[Tim Roberts]

Sorry that the syntax I supplied is not working, but I have to admit to a
bit of confusion. ...

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])

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.

 

[Sue Hoegemeier]

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

 

[Tim Roberts]

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)

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?

 

[Sue Hoegemeier]

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

 

[Ron Hinds]

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

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

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.

 

[Sue Hoegemeier]

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

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

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

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]

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

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.

 

[Ron Hinds]

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.

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]

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


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]

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

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.

 

[Ron Hinds]

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

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

If so, then this whole discussion has been kind of silly.

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

 

[Sue Hoegemeier]

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

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

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

If so, then this whole discussion has been kind of silly.

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

 

[Law]

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.

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

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

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.