Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

UPDATE...SELECT....

Posted on 2004-08-12
6
Medium Priority
?
275 Views
Last Modified: 2010-04-17
I want to execute a selection-query in VB with MS Access as the backend thru which I'd like to UPDATE a field based on the output from a SELECT query?Is it possible?It seems MS Access doesn't support this.
0
Comment
Question by:EXwithRaj
6 Comments
 

Expert Comment

by:dkinjal
ID: 11781736
Dear ExWithRaj,
U r little unclear with what you have said... one thing is for sure that u cannot change Db content by a Select Query.. But ya, if u meant nested queries then MS Access supports all the clauses of SQL...
if it dosent answers your question then feel free to ask...
-Kinjal
0
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 11782168
See:
http://www.devguru.com/Technologies/jetsql/quickref/update.html

if your Update statement need work with JOIN, try like:

UPDATE Table1 INNER JOIN Table2
ON Table1.Field1=Table2.Field2
SET Table1.myValue = False
WHERE Table2.myValue=False;

etc..

of course, post your statement here if necessary.

regards
0
 

Accepted Solution

by:
goolee earned 120 total points
ID: 11782656
Hi,
lets say you want to update f1 from t1 to be f1 from t2 where f2 in t2 is "xxx":

UPDATE t1, t2 SET t1.f1 = t2.f1
WHERE t2.f2 in (select f2 from t2 where f2="xxx")
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Expert Comment

by:willbuochs
ID: 11783131
Summarising your question:

I want to Update a record in one table based on data derived from a select query, using a single SQL statement?  Correct?

If so the first answer gives the solution, using nested SQL queries and the execute method of an ADODB Connection to the Backend database:

The SQL would look something like this

Update T_1 Inner Join {Select a, b From T_2 INNER JOIN T_3 ON ........  WHERE .....} As Q_1 ON T_1.a=Q_1.a
Set [T_1].[b]=[Q_1].[b]

I have found MS Access needs the square brackets sometimes, and not others!

If a string variable (sqlstr) is set to the SQL statement running the command is as follows

set cnn=new ADODB.connection

cnn.open "<type in correct connection string here - check Microsft Knowledge base for examples or VBA Help function>"
cnn.execute sqlstr

set cnn=nothing

hope this helps

Will
0
 
LVL 7

Expert Comment

by:Tanglin05
ID: 11784639
If you are using VB, you can always run your select statment and then use the results (perhaps stored in an array or custom type) in an update command.

SO...

Dim dbConn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim rslts(10) As String
Dim strSELECT, strUPDATE, strConn As String
Dim i As Integer

strConn = "<connection string>"

strSELECT = "SELECT ... FROM ... WHERE ;"

dbConn.Open strConn

rst.Open strSELECT, dbConn, adOpenForwardOnly, adLockReadOnly
If rst.EOF <> True Then
    rst.MoveFirst
    Do While Not rst.EOF
         rslts(i) = rst("<whatever>")
         i = i + 1
         rst.MoveNext
    Loop
End if
rst.Close
Set rst = Nothing

i = 0
For i = 0 To UBound(aryRslts)
     strUPDATE  = "UPDATE <table> SET (<field> = '"& aryRslts(i) &"') WHERE <field> = '<something>';"
     dbConn.Execute strUPDATE
Next

dbConn.Close
Set dbConn = Nothing

Now is this the prettiest, most efficient way to go? Not really. But it will get the job done and -can- be easier to troubleshoot if you are unfamiliar with larger, more complicated SQL statements.  You would need to add error checking and logic to the update statements so you don't continually update the same record, but this will get you going.  Will's solution also appears to be good, just depends on what you're trying to do.

Let me know if you have questions and I'll be happy to help. Thanks~


0
 

Author Comment

by:EXwithRaj
ID: 11863246
willbuochs,the action query you've stated may look very,very exotic - but does it work?
At 1st sight I was a bit curious,but upon testing it,both at the frontend & backend,the result didn't surprise me - "Syntax error in UPDATE statement"!How can you insert curly brackets in a SQL statement?It's allowed only in SHAPE statement.In the 1st place,have you tested the solution before forwarding it to me?
      And Tanglin05,your technique will work only if something is impossible to achieve thru the usual SQL codings.But by problem is not something impossible to achieve using SQL.This is proved by goolee's solution.Thanks goolee,for that.
      Besides,in Oracle it's a lot more easier.You can state the SELECT code as follows:
"UPADTE.....SET <field> =(<sub-query>)".
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Make the most of your online learning experience.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Loops Section Overview

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question