473,326 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Problem with SELECT in UPDATE Statement

I need to update values in a table (where they are null or zero) with
values from another table for the corresponding PopulationMemberID.

Now I now that you can't update where there is a join.. but the join
is in the nested select.. so it should work right?

CurrentDb.Execute ("UPDATE Samples SET
samples.SampleMemberEmployees=(SELECT pop.PopMemberEmployees FROM
population as pop WHERE [Pop].[PopulationMemberID]=[samples].
[PopulationMemberID]) WHERE nz(sampleMemberEmployees,0)=0 AND
SurveyID=" & CurrentSurvey)

when the query is executed i get: :"Operation must use an updatable
query. (Error 3073)"

I worked around this by creating a vba function accepting the Id as a
parameter.. but its slow..

Apr 20 '07 #1
1 4316
On Apr 20, 4:38 am, Mikle <mikl...@gmail.comwrote:
I need to update values in a table (where they are null or zero) with
values from another table for the corresponding PopulationMemberID.

Now I now that you can't update where there is a join.. but the join
is in the nested select.. so it should work right?

CurrentDb.Execute ("UPDATE Samples SET
samples.SampleMemberEmployees=(SELECT pop.PopMemberEmployees FROM
population as pop WHERE [Pop].[PopulationMemberID]=[samples].
[PopulationMemberID]) WHERE nz(sampleMemberEmployees,0)=0 AND
SurveyID=" & CurrentSurvey)

when the query is executed i get: :"Operation must use an updatable
query. (Error 3073)"

I worked around this by creating a vba function accepting the Id as a
parameter.. but its slow..
Try something like this (it should be sufficiently fast):

dim db as dao.database, r as dao.recordset, q as dao.recordset, s as
string
set db=currentdb
q=db.createquery("")
s="Select ...;"
set r=db.openrecordset(s, dbopensnapshot)
r.movefirst (Not really necessary)
do while not r.eof
s="Update Samples set SampleMembersEmployees = " & r!
PopMemberEmployees _
& " WHERE ..." (fill in correct statement here)
q.sql = s
q.execute
r.movenext
loop
close r
set r=nothing
set q=nothing

This should be fairly fast and quite a bit quicker than using the VBA
function.

-- Larry Engles
Access developer since day 1 of Access 1.0


Apr 20 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Nicolas Payre | last post by:
Hi, I have the following SQL that I want to use to update a table. It doesn't work ! Does someone knows why? ** I Know it could be done easy with a CURSOR FOR LOOP, but still... Thanks for...
4
by: 001 | last post by:
Hello, The select statement needs only 1 second to complete the query. But the update statement spends 30 minutes. Why? SELECT STATEMENT: declare @IDate smalldatetime select @IDate=col001...
2
by: Ray | last post by:
I have a list of about 20,000 rows that I am updating. I loop through each row in my program and basically do the following (1) select * from TABLE where SID= for update /*lock the row*/ (2)...
15
by: Hemant Shah | last post by:
Folks, We have an SQL statement that was coded in an application many years ago (starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer does not use optimal path to access the...
4
by: Mervin Williams | last post by:
I have several tables involved in my application, but the two in question here are the company and address tables. The company table has business_address_id and mailing_address_id columns, which...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
3
by: shark | last post by:
Hi all. i am facing a deadlock problem .i have included the -t1204 and -T3605 trace flags and have got the following o/p pu tin sqls server logs. 2006-06-01 17:49:21.84 spid4 2006-06-01...
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
0
by: Gagan Sindhu Dewangan | last post by:
Below is the following procedure where I am facing the problem, the work of the below procedure is to update the database. query result:sp_helptext bProjectUpdate Text ...
2
osward
by: osward | last post by:
Hello there, I am using phpnuke 8.0 to build my website, knowing little on php programing. I am assembling a module for my member which is basically cut and paste existing code section of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.