?
Solved

Update Query from same table

Posted on 2014-07-25
8
Medium Priority
?
388 Views
Last Modified: 2014-07-25
In MS Access I need to update two records(ProductID = 112,113) from   third record( ProductID  = 111) in the same table.
e.g  table tblProduct.

Product_ID            Color             Manufacture            ImportedYesNo             Product Group
      111            Black            Domestic                   No                  AA
      112                                                            
      113                                                                        
      114            White            Foreign                        Yes                  BB
      115            White            Domestic                                    No                                         BB
0
Comment
Question by:Taras
[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
8 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40219456
update with join looks like this in Access:
http://www.techonthenet.com/access/queries/update2.php
Or better, see the comment on 2009-09-16 from our top DB Guru:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

So ...
Update tblProduct T1 Inner Join tblProduct T2 on T1.ProductID in (112, 113) and T2.Product_Id = 115
Set T1.Color = T2.color, T1.Manufacture=T2.manufacture, T1.ImportedYesNo=T2.ImportedYesNo, T1.ProductGroup = T2.ProductGroup

Open in new window

0
 

Author Comment

by:Taras
ID: 40219580
Sorry I did not put question properly. Values for records which need to be updated and record which is used for update are in different tables.
So table Product is the same but there are next two tables.
This is table that has value of Product ID’s which will be updated in table tblProduct.
tlbRecordsToBeUpdated(Only one filed with two records  for update)
ProductID
112
113

This is table that has the record  from table tblProduct , the record that will be used for update.
TblRecordUsedForUpdate (only one record)
Product_ID      Color             Manufacture            ImportedYesNo       Product Group
111            Black            Domestic             No                  AA
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40219607
Whether you are joining a table to itself or to another table, the syntax is the same.  Just replace the second instance of the first table name to the name of the second table.

The larger question is what is the common field?  Surely you are not just updating two specific records.  Given that, the suggested solution won't be much help.  I don't see anything in record 111 that would link it to records 112 and 113 in the other table.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40219631
It would be helpful to see a screen shot of the Relationships diagram (if you haven't made any links, that would be a problem).
0
 

Author Comment

by:Taras
ID: 40219738
Part Hartman.
There are three tables not two.
How they are related?

Table tblProduct has Product ID that is related to TblRecordUsedForUpdate .ProductID.
And it has all records in.
Table tlbRecordsToBeUpdated  has only records ProductID's  that need to be updated and those ProudutID' are in TblProduct too.

It means three tables One where All records exist.
And two other. One of those two has and always will have  only one complete record which is identical as that in tblProduct and is used for update and Second table has only values of ProductID' that has to be updated in tblProduct.
Do not if this help.
0
 

Author Comment

by:Taras
ID: 40219745
PatHartman, apologize for name misspelling.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40220106
I am still missing how the tables are used.  Are you trying to update tblProduct with data from TblRecordUsedForUpdate  using tlbRecordsToBeUpdated  to specify which records will be updated?  So in this scenario, tblRecordsToBeUpdated will contain only the productID and is used only for the purpose of controlling which records get changed.

It is really unusual to have three tables in this scenario so we need to understand the purpose of each.  If I have described the usage correctly, add all three tables to the QBE.  Draw join lines connecting ProductID between tblProduct and tblRecordsToBeUpdated and then from tblRecordsToBeUpdated to tblRecordUsedForUpdate.  Then select all the columns from tblProduct that will be updated (not the PK) and change the query type to Update.  Then in the UpdateTo cell for each column type in the name of the column from tblRecordUsedForUpdate including the table name.
0
 

Author Closing Comment

by:Taras
ID: 40220243
Thanks Pat.
Thanks for your suggestion, in broader context this table,  tblRecordsToBe updated has records that belong to a specific group. In that specific group belongs  record  in table tblRecordUsedForUpdate used for update.
You are right in first part of your answer.Table  “tblRecordsToBeUpdated”  only has role to identify  those records which will be updated in tblProucts.
You missed  one small thing but  very important, that table had just one field.
 I added another field named Link_ProductID (it has the same value as s ProductID  in tblRecordUsedForUpdate)  and  it is same for all records in table.
That field is used for connection with tblProduct, otherwise without that field your second part of answer would not be  possible.
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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

741 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