Solved

problems with Update query with sub-select

Posted on 2004-03-25
3
1,240 Views
Last Modified: 2010-10-06
I have two tables, Table1 and Table2 of the following structure:

Table1
_______
PersonNumber - Primary key
RecordID
Name
Address
Phone
EmailAddress


Table2
________
RecordID
Email


RecordID is a numeric text field. In Table1 all RecordID are blanks. Table1 and Table2 are related by Table2.Email and Table1.EmailAddress fields. What I want is to transfer RecordID from Table2 to Table1.

I have the following sql statement:

UPDATE Table1 INNER JOIN Table2
ON Table1.EmailAddress=Table2.Email
SET Table1.RecordID=(SELECT Table2.RecordID FROM Table2)

But when I try to run this query I get an error "Operation must use an updateable query"

I'm using ms access
0
Comment
Question by:YZlat
[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
3 Comments
 
LVL 23

Accepted Solution

by:
heer2351 earned 80 total points
ID: 10682099
Your syntax is almost correct:

UPDATE Table1 INNER JOIN Table2
ON Table1.EmailAddress=Table2.Email
SET Table1.RecordID=Table2.RecordID
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10682121
Same thing, different syntax:

UPDATE Table1 SET RecordID = Table2.[RecordID] FROM Table1,Table2 WHERE Table1.[EmailAddress]=Table2.[Email]
0
 
LVL 35

Author Comment

by:YZlat
ID: 10682176
perfecto!
thanks
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

707 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