problems with Update query with sub-select
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
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
Table1
_______
PersonNumber - Primary key
RecordID
Name
Address
Phone
EmailAddress
Table2
________
RecordID
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
perfecto!
thanks
thanks
UPDATE Table1 SET RecordID = Table2.[RecordID] FROM Table1,Table2 WHERE Table1.[EmailAddress]=Tabl