?
Solved

UPDATE SELECT FROM Same Table

Posted on 2011-09-28
9
Medium Priority
?
428 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
[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
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 40

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 

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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

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