Solved

UPDATE...SELECT....

Posted on 2004-08-12
6
269 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 51

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 30 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modify a small python script 19 122
Auto-indent certain lines in Notepad++ 10 145
programming a polycom voip phone 3 57
Where to download python requests library 6 66
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

726 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