Solved

UPDATE SELECT FROM Same Table

Posted on 2011-09-28
9
387 Views
Last Modified: 2012-05-12
Hello Experts,

I'm trying to copy some values from one rec to another within the ame table for repeat orders.

For example, how can i do something like:

UPDATE tbl
  SET fld1 = fld1, fld2 = fld2, fld5 = fld5

  WHERE id of source rec = 123
  WHERE id of target rec = 456

As you see, i purposely omitted fld3 and fld4,, as i need those values unchanged.

How can i do this?
0
Comment
Question by:APD_Toronto
9 Comments
 
LVL 10

Expert Comment

by:conagraman
ID: 36719028
you are on the right track just use an sql update query

Dim yourSQL As String
Dim var1 As String
Dim var2 As String

var1 = "yourtext"
var2 = "yourtext2"

yourSQL = "UPDATE table SET tableFieldName = " & val1 & ",  secondtablename = " & val2 & " WHERE Id= 123 AND targetID = 456 ;"

'DoCmd.SetWarnings False
DoCmd.RunSQL yourSQL
'DoCmd.SetWarnings True
0
 
LVL 10

Expert Comment

by:conagraman
ID: 36719052
i just noticed i have var1 and val1  and var2 and val2   they should not be different
change the val to var or var to val : )
0
 
LVL 39

Expert Comment

by:als315
ID: 36719056
You can use this query:

UPDATE Tbl INNER JOIN (SELECT Tbl.Field1, Tbl.Field2, Tbl.Field5, 456 AS ID FROM Tbl WHERE (((Tbl.ID)=123))) As Qry1
 ON Tbl.ID = Qry1.ID SET Tbl.Field1 = [Qry1]![Field1], Tbl.Field2 = [Qry1]![Field2], Tbl.Field5 = [Qry1]![Field5]
WHERE (((Tbl.ID)=456));

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 36719073
update tbl a, tbl b
  SET a.fld1 = b.fld1, b.fld2 = a.fld2, a.fld5 = b.fld5
where a.id = 456 and b.id = 123
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:APD_Toronto
ID: 36719235
als, can you explain your code?

also, everone, note that there is only 1 table in question.

i'm almost tempted to use 2 recordsets here
0
 
LVL 10

Accepted Solution

by:
conagraman earned 500 total points
ID: 36719282

Dim yourSQL As String ' this is a string to hold your SQL Update query
Dim var1 As String ' this is a string variable to hold the information you want to put into your record
Dim var2 As String ' this is a string variable to hold the information you want to put into your record you can make as many as needed

var1 = "yourtext"    '  this is where you add text to your variable
var2 = "yourtext2"     '  this is where you add text to your second variable

yourSQL = "UPDATE table SET tableFieldName = " & var1 & ",  secondtablename = " & var2 & " WHERE Id= 123 AND targetID = 456 ;"

DoCmd.SetWarnings False 'this keeps a popup from appearing asking you if you want to update the records
DoCmd.RunSQL yourSQL ' this runs the sql statement
'DoCmd.SetWarnings True 'this turns the popups back on < you want the popups on to warn you of problems just not this one
0
 
LVL 32

Expert Comment

by:awking00
ID: 36719307
Can you post some sample data and what you expect it to be after the update?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36761955
>Accepted Solution:  Are you kidding?
0
 
LVL 10

Expert Comment

by:conagraman
ID: 36818249
dgmg? What gives?
i assumed he just wanted to know how to run an update query so i showed him how. What’s wrong with that?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

25 Experts available now in Live!

Get 1:1 Help Now