Update with subquery

  • Thread starter Thread starter ChrisM
  • Start date Start date
C

ChrisM

Hi Guys,

All I want to do is the following::

UPDATE partsMasterFile
SET stockQuantity =
(
SELECT sum(stockQuantity)
FROM partsDetails
)

This sort of thing works fine with Oracle, SQL Server etc.
But Access seems to have a problem with it. It says 'Operation Must Use
updateable query'...

Does anyone have a way around this? Suggestions involving userdefined
functions are of no use as I am need to call the query from a VB program
(via ADO) and the JET engine doesn't support userdefined functions in this
way. (great!)

I anyone can help, I would really appreciate it as it is really dpoing my
head in :-(

Best Regards,

ChrisM
 
Surely you can use VB code to calculate the sum you want
and then construct the SQL String for the update "in-line"
using the "literal" value of the sum calculated
previously. Yes / No / Maybe?

If you only update one value, you can also use Recordset
to do the update.

HTH
Van T. Dinh
MVP (Access)
 
Thanks for your reply.
Yea sure I can do that, but since I need to query the database to find the
total, that means 2 round-trips: One to get the total then another to write
it back again. That is certainly an option, I was just hoping to find a way
of doing it in a single round-trip as I have always been able to do with
bigger databases like Oracle and SQLServer.

Does anyone know how to do such an update in Access? (or can anyone state
categorically that Access cannot do it?)

Regards,

Chris.
 
I can certainly do it in a Query executed *natively* in Access using a
DSum() function instead of the SubQuery. However, you want to execute the
Query externally from Access and I don't think DSum, like the UDF, will be
recognised.

A small correction if I may: you are NOT using Access. You are using the
JET database (MDB file) to store data, not an Access database. The default
database engine used in Access just happens to be the same JET database
engine you used in your VB application.

Also, another point to note is that you are storing calculated values which
is not recommended by the Relational Database Design Principle (RDDP).
Access is designed to work efficiently and easily with databases that follow
the RDDP. One you go out of this scope, things tend to get a bit harder.

Suggest you ask the VB newsgroups for the VB solution as we tend to be a bit
Access-centric in these Access newsgroups.
 
Thanks very much for your advise,

Forgot all about DSUM (doh!) however it does work from my code and has
solved my problem, Thank you very much!!


Yes I know, apologies if I upset the purists by using the terms a bit
interchangeably ;-) I said Access because I was doing my tests in the
Access environment even though the finished query is being called through
the JET engine from VB.
* Incidentally, you say the DEFAULT database engine used by Access is JET,
what else can Access use?
harder.

Couldn't agree more, unfortunatly I have inherited the database structure,
and the timescale of the project prohibits a redesign...

OK, I can take a hint, I know when I'm not wanted...I'll get my coat. ;-)

Finally, thanks once again for your help, it is much appreciated.

Cheers,

ChrisM
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)



ChrisM said:
Forgot all about DSUM (doh!) however it does work from my code and has
solved my problem, Thank you very much!!
Click to expand...

Surprised. I thought DSum is an Access function. It sounds like DSum is
also a JET function.


* Incidentally, you say the DEFAULT database engine used by Access is JET,
what else can Access use?
Click to expand...

MSDE, MS-SQL Server, Oracle, SyBase, DB2 ... you name it.


OK, I can take a hint, I know when I'm not wanted...I'll get my coat.
Click to expand...
;-)

No, I didn't mean that. The problem is that responses from these newsgroups
are likely to be Access-centric which may or may not work in your VB/JET
application. I may be the worst of the lot (since my knowledge of
stand-alone VB is limited) so I actually tend to shy away from VB/JET
questions. OTOH, responses from VB newsgroups are VB-centric and more
likely to suit your set-up/requirements.

You are certainly welcome to ask here since these are public newsgroups but
be beware that the responses generally apply to Access applications and may
not apply to VB/JET application.
 
Back
Top