Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

UPDATE SELECT FROM Same Table

Posted on 2011-09-28
9
Medium Priority
?
439 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
Independent Software Vendors: 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 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

609 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