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
Solved

UPDATE SELECT FROM Same Table

Posted on 2011-09-28
9
402 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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 …

840 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