• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

UPDATE...SELECT....

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
EXwithRaj
Asked:
EXwithRaj
1 Solution
 
dkinjalCommented:
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
 
Ryan ChongCommented:
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
 
gooleeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
willbuochsCommented:
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
 
Tanglin05Commented:
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
 
EXwithRajAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now