Solved

UPDATE...SELECT....

Posted on 2004-08-12
6
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 52

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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This is an explanation of a simple data model to help parse a JSON feed
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
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 …

734 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