Solved

UPDATE...SELECT....

Posted on 2004-08-12
6
263 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 49

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This is about my first experience with programming Arduino.
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 …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now